Declaring and using cursors in dynamic SQL queries

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi, i'm trying to do the following yet i can't seem to get any print message:

SQL

SET @dynamicSQLCommandC = 
		'DECLARE test_cursor CURSOR
		FOR 
		SELECT *
		FROM ' +@CurrentTable+ 
		'
		OPEN test_cursor	
		FETCH NEXT FROM test_cursor INTO ' +@CurrentRow+ '
		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT ''Your dynamically created Cursor works mate!''
			FETCH NEXT FROM test_cursor INTO ' +@CurrentRow+ '
		END
		CLOSE test_cursor
		DEALLOCATE test_cursor'
EXEC sp_executesql @dynamicSQLCommandC

I have tested the part below dynamically in the same scope and it works so the problem doesn't stem from it:

SQL

SELECT *
FROM ' +@CurrentTable

Dynamic SQL form:

SQL

SET @dSQL =
        '
        SELECT *
        FROM ' +@CurrentTable
EXEC sp_executesql @dSQL

Thanks for your guidance.

Posted 15-Sep-14 23:13pm

YourAverageCoder

Updated 15-Sep-14 23:20pm

v6

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

hypermellow · Accepted Answer · 2014-09-15T23:40:00

Solution 1

Hi,

Do you need to open and iterate through the cursor dynamicaly?

What happens when you declare your cursor like below, and open and iterate outside of the dynamic sql statement?

SQL

DECLARE @SQL NVARCHAR(4000)
SET @SQL = 
  'DECLARE test_cursor CURSOR FOR 
  SELECT * FROM ' +@CurrentTable

EXEC sp_executesql @SQL

OPEN test_cursor	
 FETCH NEXT FROM test_cursor INTO @CurrentRow
 WHILE @@FETCH_STATUS = 0
  BEGIN
   PRINT 'Your dynamically created Cursor works mate!'
  FETCH NEXT FROM test_cursor INTO @CurrentRow
  END
CLOSE test_cursor
DEALLOCATE test_cursor'

Hope it helps, and let me know how you get on?

Posted 15-Sep-14 23:40pm

hypermellow

Comments

YourAverageCoder 16-Sep-14 6:02am

That works but i need to know if cursors work inside a dynamic SQL Command.

hypermellow 16-Sep-14 6:22am

I don't think you can declare, open and iterate a cursor inside a dynamic sql command.

Here's an article you should read:
http://www.codeproject.com/Articles/489617/CreateplusaplusCursorplususingplusDynamicplusSQLpl

Hope it helps.

YourAverageCoder 16-Sep-14 6:26am

It helps a lot. Thanks.

hypermellow 16-Sep-14 6:28am

Glad it helped. Cheers.

Declaring and using cursors in dynamic SQL queries

1 solution

Solution 1

Add your solution here

Preview 0

Declaring and using cursors in dynamic SQL queries

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us