Dynamic Cursor with sp_executesql and OpenXML

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