CURSOR声明中的变量SQL [英] Variable SQL in a CURSOR declaration

问题描述

今天我遇到了一个棘手的问题。我正在尝试执行以下操作....



1 ..取决于变量取决于我运行的SQL语句

2 ..问题在于我想要更改光标内的Select语句

3 ..我正在运行一个函数



An示例如下

Hi,

I have encounter a tricky problem today. I am trying to do the following....

1..Depending on a variable depends on what SQL statement I run
2..Where the problem is that I want to change the Select statement within the cursor
3..I am running a function

An example is as follows

DECLARE dupCursor CURSOR FAST_FORWARD

FOR SELECT IdNum, Name, Address, AgentName FROM vwEmployee where IdNum = @AidNum 

OPEN dupCursor
.
.
.

你怎么能把这样的东西放在

How can you put something like this in

DECLARE @sql VARCHAR(MAX)

IF IdNum = 0
SET @SQL = 'SELECT IdNum, IdNum2, Name, Address, AgentName FROM vwPRINTValuationNotice where IdNum = @AidNum
 
ELSE SET @SQL = 'SELECT IdNum, Name, Address, AgentName FROM vwPRINTValuationNotice where IdNum2 = @AidNum 


DECLARE dupCursor CURSOR FAST_FORWARD

FOR @sql

OPEN dupCursor
.
.
.

推荐答案

您需要在@SQL动态变量中包含游标声明，例如

You will need to include the cursor declaration in the @SQL dynamic variable e.g.

DECLARE @SQL NVARCHAR(1024) -- or what ever the size
SET @SQL = 'DECLARE dupCursor CURSOR FOR SELECT IdNum, IdNum2, Name, Address, AgentName FROM vwPRINTValuationNotice where '

IF IdNum = 0
     SET @SQL = @SQL + 'IdNum = ' + @AidNum
ELSE 
     SET @SQL = SQL + 'IdNum2 = ' + @AidNum

然后使用EXEC执行SQL

Then use EXEC to execute the SQL

EXEC sp_executesql @SQL
OPEN dupCursor
FETCH NEXT FROM dupCursor INTO @NEWREF
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @REF + ' ' + @NEWREF
	FETCH NEXT FROM dupCursor INTO @NEWREF
END
CLOSE dupCursor
DEALLOCATE dupCursor		
FETCH NEXT FROM CUR1 INTO @REF

这篇关于CURSOR声明中的变量SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！