This is prolly more of a gut check, but needed to know if this looks right.
I am making another Scalar function..
CREATE FUNCTION [dbo].[EvalTradeCode]
(
@.tradeSymbol char(15)
)
RETURNS int(1)
AS
BEGIN
Declare @.intOffset int
If (left(tradesymbol, 1) = '@.')
If (isnumeric(left(right(tradesymbol, 6), 1))
@.intOffset = 1
If left(tradesymbol, 1) = '+'
If isnumeric(left(right(tradesymbol, 6), 1)
@.intOffset = 1
IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
If isnumeric(left(right(tradesymbol, 5), 1)
@.intOffset = 1
RETURN @.intOffset
END
I was getting an error because I was using the 'then' statement in there
(remember..i'm a VB programmer... and I did check out the bol site..lol)
I took the 'then' statements out. and now the only error that comes up is th
e:
'Oncorrect syntax near '@.intOffset' ' Error.
I'm not sure if this is because of the way that it's being used in the
function, or if i've got something bass ackwards.
Thanks for your input!
~Doc
www.krushradio.com - Internet Radio for the rest of usTry replacing
@.intOffset = 1
with
SET @.intOffset = 1
HTH
Vern
"Daniel Regalia" wrote:
> This is prolly more of a gut check, but needed to know if this looks right
.
> I am making another Scalar function..
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
> @.tradeSymbol char(15)
> )
> RETURNS int(1)
> AS
> BEGIN
> Declare @.intOffset int
> If (left(tradesymbol, 1) = '@.')
> If (isnumeric(left(right(tradesymbol, 6), 1))
> @.intOffset = 1
> If left(tradesymbol, 1) = '+'
> If isnumeric(left(right(tradesymbol, 6), 1)
> @.intOffset = 1
> IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
> If isnumeric(left(right(tradesymbol, 5), 1)
> @.intOffset = 1
> RETURN @.intOffset
> END
> I was getting an error because I was using the 'then' statement in there
> (remember..i'm a VB programmer... and I did check out the bol site..lol)
> I took the 'then' statements out. and now the only error that comes up is
the:
> 'Oncorrect syntax near '@.intOffset' ' Error.
> I'm not sure if this is because of the way that it's being used in the
> function, or if i've got something bass ackwards.
> Thanks for your input!
> ~Doc
> --
> www.krushradio.com - Internet Radio for the rest of us|||Not one.. there are lots of changes :)
no offences.
Here is the function.. Hope this helps.
CREATE FUNCTION [dbo].[EvalTradeCode]
(
@.tradeSymbol char(15)
)
RETURNS int
AS
BEGIN
Declare @.intOffset int
If (left(@.tradeSymbol, 1) = '@.')
If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
set @.intOffset = 1
If left(@.tradeSymbol, 1) = '+'
If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
set @.intOffset = 1
IF left(@.tradeSymbol, 1) <>'@.' and left(@.tradeSymbol, 1) <> '+'
If isnumeric(left(right(@.tradeSymbol, 5), 1)) = 1
set @.intOffset = 1
RETURN @.intOffset
END|||Gave it a shot....it didn't like it
Incorrect syntax near the keyword 'Set'
If (left(tradesymbol, 1) = '@.')
If (isnumeric(left(right(tradesymbol, 6), 1))
Set @.intOffset = 1
If left(tradesymbol, 1) = '+'
If isnumeric(left(right(tradesymbol, 6), 1)
Set @.intOffset = 1
IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
If isnumeric(left(right(tradesymbol, 5), 1)
Set @.intOffset = 1
--
www.krushradio.com - Internet Radio for the rest of us
"Vern Rabe" wrote:
> Try replacing
> @.intOffset = 1
> with
> SET @.intOffset = 1
> HTH
> Vern
> "Daniel Regalia" wrote:
>|||I feel it can better be written this way. You can validate better than me.
You should be a procedural logic expert :) Let me know.
CREATE FUNCTION [dbo].[EvalTradeCode]
(
@.tradeSymbol char(15)
)
RETURNS int
AS
BEGIN
Declare @.intOffset int
Set @.intOffset = 0
If (left(@.tradeSymbol, 1) = '@.') or (left(@.tradeSymbol, 1) = '+')
begin
If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
set @.intOffset = 1
end
else
begin
If isnumeric(left(right(@.tradeSymbol, 5), 1)) = 1
set @.intOffset = 1
end
RETURN @.intOffset
END|||None Taken...
It's a learning experience for me :D. Just add this question to my beer
tab. Thanks OmniBuzz
~Doc
www.krushradio.com - Internet Radio for the rest of us
"Omnibuzz" wrote:
> Not one.. there are lots of changes :)
> no offences.
> Here is the function.. Hope this helps.
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
> @.tradeSymbol char(15)
> )
> RETURNS int
> AS
> BEGIN
> Declare @.intOffset int
> If (left(@.tradeSymbol, 1) = '@.')
> If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
> set @.intOffset = 1
> If left(@.tradeSymbol, 1) = '+'
> If isnumeric(left(right(@.tradeSymbol, 6), 1)) = 1
> set @.intOffset = 1
> IF left(@.tradeSymbol, 1) <>'@.' and left(@.tradeSymbol, 1) <> '+'
> If isnumeric(left(right(@.tradeSymbol, 5), 1)) = 1
> set @.intOffset = 1
> RETURN @.intOffset
> END
>|||Sure Sir. I remember the first one you promised too..
Anything for a beer :)
"Daniel Regalia" wrote:
> None Taken...
> It's a learning experience for me :D. Just add this question to my beer
> tab. Thanks OmniBuzz
> ~Doc
>
> --
> www.krushradio.com - Internet Radio for the rest of us
>
> "Omnibuzz" wrote:
>|||One problem is that your parenthesis are not properly matching up. Another,
and this is just personal preference, is you are not using begin and end to
group your if else logic. I prefer to have a begin and end for every if
statement, and indent accordingly. It makes the code easier to follow, and
leaves no confusion as to the order of nested ifs.
"Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in message
news:4618C615-2579-4999-B0BC-3DB935F7527F@.microsoft.com...
> This is prolly more of a gut check, but needed to know if this looks
right.
> I am making another Scalar function..
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
> @.tradeSymbol char(15)
> )
> RETURNS int(1)
> AS
> BEGIN
> Declare @.intOffset int
> If (left(tradesymbol, 1) = '@.')
> If (isnumeric(left(right(tradesymbol, 6), 1))
> @.intOffset = 1
> If left(tradesymbol, 1) = '+'
> If isnumeric(left(right(tradesymbol, 6), 1)
> @.intOffset = 1
> IF left(tradesymbol, 1) <>'@.' and left(tradesymbol, 1) <> '+'
> If isnumeric(left(right(tradesymbol, 5), 1)
> @.intOffset = 1
> RETURN @.intOffset
> END
> I was getting an error because I was using the 'then' statement in there
> (remember..i'm a VB programmer... and I did check out the bol site..lol)
> I took the 'then' statements out. and now the only error that comes up is
the:
> 'Oncorrect syntax near '@.intOffset' ' Error.
> I'm not sure if this is because of the way that it's being used in the
> function, or if i've got something bass ackwards.
> Thanks for your input!
> ~Doc
> --
> www.krushradio.com - Internet Radio for the rest of us
Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts
Monday, March 12, 2012
Nested Cursors
What is the best way to nest cursors?
This code does not seem to be returning me all of the data.
Code Snippet
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @.TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno
--delete from tblElementCPO
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.Count = count (*)
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
if @.Count > 0
begin
select @.ProjTypeRecno = ProjTypeRecno
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
select @.Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @.TemplateRecNo
and ProjTypeRecno = @.ProjTypeRecNo
if @.Count > 0
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo,
PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,
Description
FROM tblElementTypeDep
WHERE (TemplateRecNo = @.TemplateRecNo)
AND (ProjTypeRecNo = @.ProjTypeRecno)
AND (PreElementTypeRecNo = @.ElementTypeRecno)
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.PreElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PreElementTypeRecno
if @.PreElementRecno is not null
begin
select @.PostElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PostElementTypeRecno
if @.PostElementRecno is not null
begin
select @.Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @.ElementTypeDepRecno
and PreElementRecNo = @.PreElementRecNo
and PostElementRecno = @.PostElementRecno
if @.Count = 0
begin
INSERT INTO tblElementDepCPO
(ElementTypeDepRecNo, PreElementRecNo,
PostElementRecNo, ElapsedTimeDueDates,
ElapsedTimePlanDates, Description,
ChangeDate, ChangePerson)
VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,
@.PostElementRecno, @.ElapsedTimeDueDates,
@.ElapsedTimePlanDates, @.Description,
GETDATE(), CURRENT_USER)
end
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.NextPlanDate = ProjectedCompletionDate,
@.NextDueDate = RequiredCompletionDate
from tblElementAttemptCPO
where ElementRecno = @.PreElementRecNo
end
else
begin
select @.NextPlanDate = @.StartDate
select @.NextDueDate = @.StartDate
end
select @.NextPlanDate =
dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)
select @.NextDueDate =
dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)
insert into tblElementAttemptCPO (ElementRecno,
ProjectedCompletionDate, RequiredCompletionDate,
ProjectedStartDate, RequiredStartDate,
ActualStartDate, ActualCompletionDate, AttemptNum,
IsCompleted, IsStarted, ResponsibleRoleTypeRecno,
ChangeDate, ChangePerson)
values (@.PostElementRecno,
@.NextPlanDate, @.NextDueDate,
'1/11/1900', '1/11/1900',
'1/11/1900', '1/11/1900', 0,
0, 0, 0,
GETDATE(), CURRENT_USER)
end
end
end
FETCH NEXT
FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
FETCH NEXT FROM element_Cursor into @.ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
end
There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?
Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.
|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.Joe
Nested Cursors
What is the best way to nest cursors?
This code does not seem to be returning me all of the data.
Code Snippet
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @.TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno
--delete from tblElementCPO
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.Count = count (*)
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
if @.Count > 0
begin
select @.ProjTypeRecno = ProjTypeRecno
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
select @.Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @.TemplateRecNo
and ProjTypeRecno = @.ProjTypeRecNo
if @.Count > 0
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo,
PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,
Description
FROM tblElementTypeDep
WHERE (TemplateRecNo = @.TemplateRecNo)
AND (ProjTypeRecNo = @.ProjTypeRecno)
AND (PreElementTypeRecNo = @.ElementTypeRecno)
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.PreElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PreElementTypeRecno
if @.PreElementRecno is not null
begin
select @.PostElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PostElementTypeRecno
if @.PostElementRecno is not null
begin
select @.Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @.ElementTypeDepRecno
and PreElementRecNo = @.PreElementRecNo
and PostElementRecno = @.PostElementRecno
if @.Count = 0
begin
INSERT INTO tblElementDepCPO
(ElementTypeDepRecNo, PreElementRecNo,
PostElementRecNo, ElapsedTimeDueDates,
ElapsedTimePlanDates, Description,
ChangeDate, ChangePerson)
VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,
@.PostElementRecno, @.ElapsedTimeDueDates,
@.ElapsedTimePlanDates, @.Description,
GETDATE(), CURRENT_USER)
end
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.NextPlanDate = ProjectedCompletionDate,
@.NextDueDate = RequiredCompletionDate
from tblElementAttemptCPO
where ElementRecno = @.PreElementRecNo
end
else
begin
select @.NextPlanDate = @.StartDate
select @.NextDueDate = @.StartDate
end
select @.NextPlanDate =
dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)
select @.NextDueDate =
dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)
insert into tblElementAttemptCPO (ElementRecno,
ProjectedCompletionDate, RequiredCompletionDate,
ProjectedStartDate, RequiredStartDate,
ActualStartDate, ActualCompletionDate, AttemptNum,
IsCompleted, IsStarted, ResponsibleRoleTypeRecno,
ChangeDate, ChangePerson)
values (@.PostElementRecno,
@.NextPlanDate, @.NextDueDate,
'1/11/1900', '1/11/1900',
'1/11/1900', '1/11/1900', 0,
0, 0, 0,
GETDATE(), CURRENT_USER)
end
end
end
FETCH NEXT
FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
FETCH NEXT FROM element_Cursor into @.ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
end
There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?
Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.
|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.Joe
Saturday, February 25, 2012
need urgent help in T-SQL
-- TOP MEDIAN
BEGIN
DECLARE @.medvarcnt int
DECLARE @.medianValue float
DECLARE @.medianfield varchar(255)
DECLARE @.SQLSTR Nvarchar(800)
SET @.medianfield = 'Cluster_Top'
CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)
SET @.SQLSTR = ('INSERT #medianlist SELECT ' + @.medianfield + ' AS medianval FROM ' + @.result_table_name + ' ORDER BY
' + @.medianfield + ' DESC')
SET @.SQLSTR = CAST (@.SQLSTR AS NVARCHAR(800))
EXECUTE sp_executesql @.SQLSTR
SET @.medvarcnt = (SELECT COUNT(*) FROM #medianlist)
IF @.medvarcnt % 2 = 0
BEGIN
--even
line17 set @.medianValue = (SELECT SUM(medianval)/2 FROM #medianlist WHERE rid >=(@.medvarcnt/2) and rid <=
(@.medvarcnt/2)+1)
line19 set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
ELSE
BEGIN
--odd
set @.medianValue = (SELECT medianval FROM #medianlist where rid =(@.medvarcnt/2)+1)
set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
DROP TABLE #medianlist
END
[addedon]March 17, 2007, 7:31 pm[/addedon]i'm trying to create a median solution with stored procedure...and from the coding i post above, i encounter the error stating failure to change from varchar to float everytime i execute it...
i suspect its happen on line 17 and 18 ...T-SQL got it as varchar...and when i use it back as variable in line 19 (whereas it suppose to take it as a float value....) thus error occur....
make it simple...@.medianvalue should be an int (let's say 18) in line 17 and 18...but at line 19 ,system still take it as whole sentence in varchar...hope everyone can understand what i try to tell...
anyone expert can provide me with solution? thanx
Try this as a replacement for Line 19:set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
..and here's an explanation as to why you should do this:
http://msdn2.microsoft.com/en-us/library/ms190309.aspx
You need to review the code sample that you provided for further occurrences of attempting to append an INT to an NVARCHAR - with a quick glance I spotted one more.
Chris
|||yes, thanks for your solution, chris...it works perfectly right now.
i should be more observant next time.
there's a problem again after undergone some testing...
i expect @.medianvalue to be equals to 5.50 (should be accurate until 2 decimal) but the result coming out is shown as 5.
is there anything wrong with my coding? i have try CAST the SUM value into FLOAT but it doesnt work...
For more information, data type of the field Top_Median that i'm going to update is FLOAT.
any help is very much appreciated.
|||
Could it be the datatype specified in this line that's causing the problem?
CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)
Chris
Subscribe to:
Posts (Atom)