Search
Monday, November 12, 2018 ..:: Home ::.. Register  Login

Peter Henry on Facebook  Peter Henry on LinkedIn  Peter Henry on Twitter

   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

Shell for creating T-SQL Cursors

Feb 23

Written by:
Sunday, February 23, 2014 4:30 PM  RssIcon

image

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)
    print @conferenceName
    --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!

 

Resources

Technet SQL Server: DECLARE CURSOR (Transact –SQL)

Technet SQL Server: Transact-SQL Cursors

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement