Dynamic Query is not working with cursor in SqlServer 2008 Stored Procedure

Question

I am building a stored procedure in Sql Server 2008. Stored Procedure has two arguments which are the column names of a table. In stored procedure I used dynamic query for fetching data of these two columns with the help of Cursor.

Code:

    Create PROCEDURE TestSP
(
@firstAttribute nvarchar(max),
@secondAttribute nvarchar(max)
)
AS 
DECLARE @x FLOAT
DECLARE @y INT
DECLARE @query nvarchar(max)
DECLARE @cursor_query nvarchar(max)
DECLARE @result_Cursor as cursor

BEGIN
SET @query = 'Select '+ @firstAttribute+','+@secondAttribute+' from TBL_TEST_DATA_NEW'
SET @cursor_query =' set @cursor = cursor for ' + @query +' open @cursor;'

PRINT 'CURSOR_QUERY'+@cursor_query

exec sys.sp_executesql
    @cursor_query
    ,N'@cursor cursor output'
    ,@result_Cursor output

FETCH NEXT FROM result_Cursor INTO @x, @y

But when I am executing this SP it is giving me following error

Msg 16916, Level 16, State 1, Procedure TestSP, Line 33
A cursor with the name 'result_Cursor' does not exist.

Execute Command :

Exec TestSP "Column_1","Column_2"

Can someone tell me why I am getting this error

Please Help..

Thanks

Add @ to your parameter result_Cursor and retry: FETCH NEXT FROM @result_Cursor INTO @x, @y — Nadeem_MK
– Nadeem_MK, Commented Sep 27, 2013 at 6:23

nimdil · Accepted Answer · 2013-09-27 06:32:36Z

1

The error is saying that Cursor does not exist. You can't start with "set cursor". Also cursor name is without @.

So try using

declare cursorNameHere cursor for SELECT ...
open cursorNameHere

answered Sep 27, 2013 at 6:32

nimdil

1,38111 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

roman · Accepted Answer · 2013-09-27 06:54:58Z

1

@result_Cursor is a variable, just change your last line to

FETCH NEXT FROM @result_Cursor INTO @x, @y

see example on sql fiddle demo

answered Sep 27, 2013 at 6:54

roman

118k30 gold badges205 silver badges209 bronze badges

Comments

amir aziz · Accepted Answer · 2013-10-10 07:53:59Z

0

change

PRINT 'CURSOR_QUERY'+@cursor_query

to

PRINT 'CURSOR_QUERY '+@cursor_query

answered Oct 10, 2013 at 7:53

amir aziz

12 bronze badges

Collectives™ on Stack Overflow

Dynamic Query is not working with cursor in SqlServer 2008 Stored Procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related