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]
