Programming, SQL, SQL Server 2008

tSQL: Create a Cursor from a Dynamically Created Select Statement

First off let me start by saying that if possible it is best to avoid using cursors since they do have their drawbacks (for starters, they suffer performance issues).

However in my opinion, there are times where cursors are very useful. There seems to be an ongoing witch hunt to vilify cursors that I do not completely agree with. Just because something can be used incorrectly does not mean that you should not know how to use it, and there are scenarios where they can be useful and/or unavoidable. Basically, I am saying to use the right tool for the right job.

I was looking into updating a stored procedure that contained a cursor and needed to make the procedure perform differently based on the parameters passed in to it. To accomplish this I set out to adjust the query conditionally in a variable and run the cursor based on the dynamically created statement. Below is example code of how I assigned the SQL to a variable and then executed the cursor:

DECLARE @MainQuery VARCHAR (8000)
If @Conditional = 90
	SET @MainQuery = 'DECLARE Qry CURSOR FOR SELECT First,Last FROM Address'
EXECUTE(@MainQuery)
OPEN Qry

Notice in the example that to execute the dynamically created SELECT statement the cursor constructor had to be included with the statement. Once that was done the statement could be executed through a standard EXECUTE statement.

The above method works like a charm and let’s me dynamically construct SELECT statements for a cursor.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s