Skip to content

Cursors and Dynamic SQL

28 November 2008

Although what I’ll talk about next isn’t rocket science and most people with some database development experience already know this, I hope there will still be some who will find it useful.

I am currently working on a SQL Server 2005 application. Whenever I had to develop a solution involving databases until now, I developed all the application logic in C# and used SQL Server only for storing data. Maybe I created a small stored procedure or view, but never got to actually implementing anything serious using only SQL. Up until now.

Suppose you have multiple data sources, each uploading data into a table and data sources can be added/removed from the solution after it is deployed. You need to copy all data from all sources into a single table (all tables have the same structure). Now this being a SQL Server application, it comes naturally that the best place to store the list of data sources is a table. Unfortunately, copying data from all tables cannot be done in a single statement. First, you need the SQL equivalent of stepping through each row. This is done using a cursor, as follows:

DECLARE Source CURSOR FOR SELECT SourceName FROM TableListingSources
DECLARE @SourceName VARCHAR(100)
OPEN Source
FETCH NEXT FROM Source INTO @SourceName

WHILE @@FETCH_STATUS = 0
BEGIN
    /* Copying things will come here */
    FETCH NEXT FROM Source INTO @SourceName
END

CLOSE Source
DEALLOCATE Source

First statement declares a cursor, assigning it a query. Second statement declares a variable which will be used to store the data retrieved by the cursor. The the source is opened. FETCH NEXT grabs the following row of the query result and stores it in the given variables. Multiple fields can be retrieved and stored by separating them with commas. @@FETCH_STATUS is 0 as long as the cursor is able to retrieve a row – in other words, as long as the end of the table isn’t reached. Cleanup at the end – the cursor must be closed and deallocated.

So executing the above, the variable @SourceName will take the value of each table name stored in the TableListingSources table. Second problem is copying data from each table, given its name. Although looking very elegant, the following statement won’t work:

INSERT INTO Destination SELECT * FROM @SourceName

There are places in a SQL statement where variables cannot be specified. And this is where Dynamic
SQL comes into play. Instead of the above statement, you can write:

EXEC (‘INSERT INTO Destination SELECT * FROM ‘ + @SourceName)

EXEC, or EXECUTE, can send to SQL Server any string and SQL Server will try to interpret it as a statement. So whenever you need to generate statements on the fly, depending on some variables that can’t be directly placed into statements, you can use EXEC and build a string instead.

More information on cusors and dynamic SQL can be found on MSDN.

From → code complete

Leave a Comment

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

%d bloggers like this: