Shell for creating T-SQL Cursors
Sunday, February 23, 2014 4:30 PM
Do you code? Do you access DBs? Do you sometimes need to create cursors? Then I think I have a link for you!
If you code for a living, chances are pretty good you access a DB in some type, shape or form. AND, chances are, you have to create SQL to test, verify, identify, investigate, analyze, etc data in that DB. AND! I’ll bet you a double-double, there are times you COULD have used a cursor, but just couldn’t for the life of you remember the syntax off the top of your head? Well I can help you! Why? Cause I do all that above as well!
I know, I know, you dbas out there are probably ready to POUNCE on me ready to tell me how bad cursors are for performance. Ya, ya, I know, I know. BUT there are sometimes you JUST CAN’T do what you want to do with set notation and need to loop through each and every row! I know I do this often enough that I know I CAN do it, but not enough to memorize the syntax. Although I still may not memorize it, I hope this blog will serve as my sql script shell!
declare @conferenceID int = 0 --var used to pump data into on each time through loop/cursor
declare conferenceCursor cursor for select id from Conference order by StartDate --declare the cursor along with the query
open conferenceCursor --need to explicitly open
fetch next from conferenceCursor into @conferenceID --need to explicitly pump data into your var from the cursor
while @@fetch_Status = 0 --loop while no errors
begin --typical begin cause there's more than one line
print @conferenceID --debugging tool
--do some work with the value you just got
declare @conferenceName varchar(max) = (select EnglishName from Conference c where c.ID = @conferenceID)
--done your work, back to shell stuff
fetch next from conferenceCursor into @conferenceID --need to get the next value, don't do this and you loop forever
end --need to bookend your begin above
close conferenceCursor --done? you need to close, reminds me of C days
deallocate conferenceCursor --same as above, need to house clean properly
This is the bare minimum….that I’ve found I’ve used in the past. TECHNICALLY there is a bit more there, BUT for 99% of all the cursors I’ve written, they follow the pattern above. Set some vars, declare the cursor, use it to do some work, then clean things up and you’re back to getting another coffee.
Speaking of coffee, now that you know how to QUICKLY get a cursor shell up and running, it’s time to grab a coffee and get coding!
Technet SQL Server: DECLARE CURSOR (Transact –SQL)
Technet SQL Server: Transact-SQL Cursors