SQL Server Cursors With Dynamic SQL

First of all I do not recommend using Dynamic SQL or Cursors in production queries or stored procedures.  But they are a necessary evil sometimes.  When I do administrative queries or sp, dynamic SQL and cursors are very handy.
 
For example I needed to populate a table from meta data in two different tables.  I need the meta data to figure out what tables are needed to be use as a source for my query.  I have to create dynamic query, because at the time of execurting the stored procedure I don’t know which table and column should be used as the source.   After creating the dynamic SQL statement, I then need to loop through the results and create an insert statement for each record returned from the cursor.  Right now I won’t go into detail of the solution, but the follwowing code example helped alot.

[sourcecode language=”sql”]
DECLARE @my_cur CURSOR
EXEC sp_executesql
N’SET @my_cur = CURSOR STATIC FOR
SELECT name FROM dbo.sysobjects;
OPEN @my_cur’
,N’@my_cur cursor OUTPUT’, @my_cur OUTPUT
FETCH
NEXT FROM @my_cur
[/sourcecode]

Leave a Reply

Your email address will not be published. Required fields are marked *