在动态SQL查询中声明和使用游标 [英] Declaring and using cursors in dynamic SQL queries

问题描述

我正在尝试执行以下操作，但我似乎无法获得任何打印消息：

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

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:

SELECT *
FROM ' +@CurrentTable

动态SQL表格：

Dynamic SQL form:

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

感谢您的指导。

Thanks for your guidance.

推荐答案

您好，



您是否需要动态打开和迭代光标？



当你将光标声明如下，open和iterat时会发生什么在动态sql语句之外？

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?

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?

这篇关于在动态SQL查询中声明和使用游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！