使用 sp_executesql 和 OpenXML 的动态游标

引言

本文档描述了如何创建一个游标，该游标使用 OpenXML 构建动态查询。

我想要创建一个游标，该游标接受在 Where 子句中使用 OpenXML 的查询。但我不知道需要查询的表或视图的名称，这由存储过程中的参数决定。因此，在搜索了很长时间后，我找到了一种解决方案。

首先，以您想要的方式创建动态查询，然后创建一个将创建 CURSOR 的字符串，并将其存储到变量中。

@SelectStatement=N'set @cursor = CURSOR FOR SELECT RolId FROM VIEW_'+ 
  cast(@Soort as varchar(15))+' 
  WHERE id IN (SELECT ID FROM OPENXML (@idoc, ''/ROOT/IDS'',1) 
  WITH (ID  varchar(10)))  OPEN @cursor'

记住在查询本身中 OPEN 游标。使用 sp_executesql 执行语句，并将游标带入局部变量。然后，您可以像使用静态游标一样使用该游标。

动态游标

DECLARE @idoc int,@ID INT, @Soort INT,@doc varchar(1000),@i INT
SET @doc ='<ROOT>
<IDS ID="4531349">
</IDS>

<IDS ID="4531363">
</IDS>

</ROOT>'
SET @Soort=600031

DECLARE @SelectStatement Nvarchar(1000)
SET @SelectStatement=N'set @cursor = CURSOR FOR SELECT RolId FROM VIEW_'+
    cast(@Soort as varchar(15))+' WHERE id IN (SELECT ID FROM OPENXML 
    (@idoc, ''/ROOT/IDS'',1) WITH (ID varchar(10))) OPEN @cursor'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

DECLARE @mainCursor cursor

exec sp_executesql @SelectStatement,
N'@cursor cursor output, @idoc INT ',@mainCursor output,@idoc


FETCH NEXT FROM @mainCursor INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @ID
   FETCH NEXT FROM @mainCursor INTO @ID
END
CLOSE @mainCursor
DEALLOCATE @mainCursor


EXEC sp_xml_removedocument @idoc