如何在 CURSOR 中创建动态 SQL 查询

问题描述：

我必须在 cursor

DECLARE @id VARCHAR(10)

declare @loc varchar(25)
set @loc = '/MainItem/SubItem';

declare @query varchar(max)     

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT * FROM @tempcolumnname

OPEN myCursor
FETCH NEXT FROM myCursor INTO @id

WHILE @@FETCH_STATUS = 0 
BEGIN
    set @query = 'SELECT * FROM OPENXML(@hdoc, '+@loc+', 3) WITH (code_db_key int)'
    exec (@query)    

    FETCH NEXT FROM myCursor INTO @id
END

但是执行这个会抛出异常

but executing this throws an exception

消息 137，级别 15，状态 2，第 1 行
必须声明标量变量@hdoc"

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@hdoc"

消息 319，级别 15，状态 1，第 1 行
关键字with"附近的语法不正确.

Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.

如果这个语句是一个公用表表达式，一个 xmlnamespaces 子句或更改跟踪上下文子句，前一个语句
必须以分号结尾.`

If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement
must be terminated with a semicolon.`

但是当我在游标外执行相同的查询时，它工作正常.

But when I am executing the same query outside the cursor, it is working fine.

答

在游标中，您必须再次执行 xml 文件，并带有 xml 输出声明.

In cursor you have to again execute your xml file , with xml output declaration.

DECLARE @id VARCHAR(25)

declare @loc varchar(25)
set @loc = '/MainItem/SubItem';

declare @query varchar(max) 

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
     SELECT * FROM @tempcolumnname

OPEN myCursor
FETCH NEXT FROM myCursor INTO @id

WHILE @@FETCH_STATUS = 0 
BEGIN
    set @query = 'DECLARE @hdoc INT;           
    EXEC sp_xml_preparedocument @hdoc OUTPUT,'''+ @info+'''
        Select Statement
        Insert Statement exec (@query)      

    FETCH NEXT FROM myCursor INTO @id       
END

CLOSE myCursor
DEALLOCATE myCursor

如何在 CURSOR 中创建动态 SQL 查询

相关推荐