nested cursors? @@FETCH_STATUS

Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am at
the end of the rowset....will the internal loop screw things up for the
external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
use that to control the loop?
Will this work or fail:
DECLARE vcursor cursor local for
select i.id from inserted
Open vcursor
FETCH NEXT FROM vcursor into @.id
WHILE @.@.FETCH_STATUS=0
BEGIN
declare acursor cursor local for
select classcode FROM CLASSES WHERE ID=@.ID
Open acursor
FETCH NEXT FROM acursor into @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM acursor into @.aclasscode
END
CLOSE acursor
DEALLOCATE acursor
END
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
eg will the internal loop reaching the last recods and setting
@.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they each
have their own 'instance' of @.@.FETCH_STATUS
Al Blake, Canberra, AustraliaYour code will work fine. You are fetching the records in each of the loop
and @.@.FETCH_STATUS contains the latest value. So it won't make any problem
Babu M K
Comat Techonologies Pvt. Ltd.
"Al Blake" <al@._delete_this_.blakes.net> wrote in message
news:%231YQkILFFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
> If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am
at
> the end of the rowset....will the internal loop screw things up for the
> external loop? Do I need to save @.@.FETCH_STATUS off to another variable
and
> use that to control the loop?
> Will this work or fail:
> DECLARE vcursor cursor local for
> select i.id from inserted
> Open vcursor
> FETCH NEXT FROM vcursor into @.id
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> declare acursor cursor local for
> select classcode FROM CLASSES WHERE ID=@.ID
> Open acursor
> FETCH NEXT FROM acursor into @.aclasscode
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> -- do processing
> --
> FETCH NEXT FROM acursor into @.aclasscode
> END
> CLOSE acursor
> DEALLOCATE acursor
> END
> FETCH NEXT FROM vcursor into @.id
> END
> CLOSE vcursor
> DEALLOCATE vcursor
> eg will the internal loop reaching the last recods and setting
> @.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they
each
> have their own 'instance' of @.@.FETCH_STATUS
> Al Blake, Canberra, Australia
>|||On Thu, 17 Feb 2005 16:32:56 +1100, Al Blake wrote:

>Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
>If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am a
t
>the end of the rowset....will the internal loop screw things up for the
>external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
>use that to control the loop?
>Will this work or fail:
Hi Al,
As Babu said: this will work. But it will probably be S-L-O-W.
From your code, I see absolutely no reason to use two nested cursors. You
can just do it in one cursor. Depending on what "-- do processing" really
is, you might even be able to do it without cursors at all.
Here's a version with just one cursor. If you need help to create a
completely set-based version, post some more information about what this
code actually does - and check out www.aspfaq.com/5006 for how to provide
the information.
DECLARE vcursor cursor local for
SELECT i.id, c.classcode
FROM inserted AS i
INNER JOIN classes AS c
ON c.ID = i.ID
Open vcursor
FETCH NEXT FROM vcursor into @.id, @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

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

|||i've changed it to type float. This solve the problem , thanks again Chris.