November 3, 2016 at 5:17 am
I've got a DECLARE CURSOR and I wish to loop through and process rows that meet certain criteria. I want to use a WHILE loop to process such rows. I know there is some code to check in that WHILE loop to finish processing when no more rows in the table meet the criteria. What does that syntax look like?
Thanks
November 3, 2016 at 5:22 am
Firstly, why are you using a cursor? It's very rare to need one in SQL, and they're slower than set-based code in the vast majority of cases.
Try set-based code first, if you can't get a set-based solution (and neither can anyone you ask for advice), then only consider a cursor
To answer your question, look up @@Fetch_Status in Books Online (the online documentation for SQL Server)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2016 at 5:29 am
scottlackey2001 (11/3/2016)
I've got a DECLARE CURSOR and I wish to loop through and process rows that meet certain criteria. I want to use a WHILE loop to process such rows. I know there is some code to check in that WHILE loop to finish processing when no more rows in the table meet the criteria. What does that syntax look like?Thanks
Quick thought, you are approaching this problem from the wrong angle, picking the tools before defining the task. Strongly suggest you post (new thread) all relevant information, table structures, sample data sets, desired result set, relevant logic etc. and ask for suggestions on how to solve the problem.
😎
November 3, 2016 at 7:56 am
is there a way to loop through rows without using a cursor? I can select my rows into a temp table and all, but need to loop through them one by one to update certain rows.
November 3, 2016 at 8:04 am
scottlackey2001 (11/3/2016)
is there a way to loop through rows without using a cursor? I can select my rows into a temp table and all, but need to loop through them one by one to update certain rows.
Yes, those are called set based methods which normally are much faster and better in all ways than row by row methods like while loops and cursors.
😎
Strongly suggest you post all relevant information, table structures, sample data sets, desired result set, relevant logic etc. and ask for suggestions on how to solve the problem!
November 3, 2016 at 8:14 am
scottlackey2001 (11/3/2016)
is there a way to loop through rows without using a cursor? I can select my rows into a temp table and all, but need to loop through them one by one to update certain rows.
Why do you think you need to process one row at a time?
November 3, 2016 at 8:21 am
scottlackey2001 (11/3/2016)
is there a way to loop through rows without using a cursor?
Why do you want to loop through the rows?
...to update certain rows.
Single UPDATE statement with a WHERE clause that returns just those certain rows. Not seeing anything here that needs a loop.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply