CODEWITHSUNDEEP
sqlsql-servercursordynamic-sql
Beau D'Amore
Beau D'Amore

Reputation: 3404

SQL Cursor execution quandry

I have a Stored Procedure that is using dynamic sql to build its complex query for a report. At one point, it is calling exec(@sql) right before opening the cursor... I believe this is a mistake and the exec(@sql) command can be removed as it is executing individually and not needed to use the cursor...

Am I right? Here's a snippet:

-- before this it's just doing string concatenation
SET @sql = N'DECLARE cc CURSOR STATIC FOR ' + @sql 
exec(@Sql) --<-- is this needed here?
OPEN cc

Doesn't opening the cursor effectively run the SQL inside the cursor?

Upvotes: 0

Views: 77

Answers (2)

Siyual
Siyual

Reputation: 16917

Yes, it is needed.

The @sql variable is prepended with the CURSOR declaration script just before the EXEC(@sql) line. It is done this way because you cannot declare a cursor with the following:

DECLARE cc CURSOR STATIC FOR EXECUTE(@sql)

You can't declare a CURSOR FOR an exec.

This was written this way to get around that by prepending the CURSOR declare statement to the dynamic SQL. The original author wasn't trying to execute it within the body of a CURSOR, but rather create a CURSOR for the result set of the dynamic SQL that was written.

So, yes, this is needed.

Upvotes: 1

Stan Shaw
Stan Shaw

Reputation: 3034

Even though I would advise against using cursors or dynamic sql unless absolutely necessary, you need to include all of your cursor manipulation (and any other sql that operates on the same dataset) inside the @sql variable. Otherwise, you're running two entirely separate sql commands.

For example:

SET @sql = N'DECLARE cc CURSOR STATIC FOR ' + @sql 
@sql = @sql + 'OPEN cc'
exec(@sql)

Upvotes: 0

Related Questions