Create Cursor using Dynamic SQL Query

As we know, Cursors are a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as such. Even though we are advised not to use or to minimize the usage of cursors in SQL, there are times which, it’s the only available option, depending on the task and the situation. But there are times you are required to build the cursor using a dynamic SQL select statement.

Here is how we create Cursor using dynamic SQL Query

Note: While creating a cursor using dynamic query, initially the cursor should be declared and passed on to the dynamic query execution.

/* VARIABLE DECLARATION */

DECLARE @ObjCursor      AS CURSOR

DECLARE @SqlQuery       AS NVARCHAR(MAX)

DECLARE @DQuery         AS NVARCHAR(MAX)

DECLARE @Value_1        AS VARCHAR(50)

DECLARE @Value_2        AS VARCHAR(50)

.

.

.

DECLARE @Value_N        AS VARCHAR(50)

/* CREATE DYNAMIC SQL QUERY */

SET @DQuery       = 'SELECT <Column_1>,<Column2_>, ... <Column_N> FROM <TABLE_NAME>'

SET @SqlQuery     = 'SET @CURSOR = CURSOR FORWARD_ONLY STATIC FOR ' + @DQuery + ' OPEN @CURSOR;'

/* HERE YOU CAN PASS DYNAMIC QUERY TO CURSORS */

EXEC SYS.SP_EXECUTESQL @SqlQuery ,N'@CURSOR CURSOR OUTPUT',@ObjCursor OUTPUT

FETCH NEXT FROM @ObjCursor INTO @Value_1,@Value_2,...,@Value_N

WHILE (@@FETCH_STATUS = 0)

BEGIN

      /*HERE YOU CAN DO ACTUALLY CODING */

     

PRINT  @Value_1,@Value_2,...,@Value_N

   

      FETCH NEXT FROM @ObjCursor INTO @Value_1,@Value_2,...,@Value_N

END

CLOSE       @ObjCursor

DEALLOCATE  @ObjCursor

About Author:

Vishal Kudale  is enthusiast .net developer who works as associate consultant with Systems Plus Pvt. Ltd. He in free time reads and write on various web technologies. He can be contacted at: vishal.kudale@spluspl.com