Introduction
This article describes how to create a cursor which takes a dynamic query built using OpenXML.
I wanted to create a cursor which takes a query having OpenXML in the Where
clause. But I did not know the name of the table or view which needed to be queried; this is decided by a parameter supplied in the Stored Procedure. So after searching a lot, I found a solution.
First, create a dynamic query the way you want to create it, and then create a string which will create the CURSOR, and store it into a variable.
@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'
Remember to OPEN
cursor in the query itself. Using sp_executesql
, execute the statement and take the cursor into the local variable. Then, you can work with the cursor like the way you work with a static cursor.
Dynamic cursor:
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