本文介绍了如何在 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"
消息 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 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!