CURSOR FOR EXECUTE problem

Hi
I have a query stored in a table i need to iterate it row by row so i am using this code
here I get the query into a variable @var


declare @get_data3  CURSOR
SET @get_data3 = CURSOR FOR

execute sp_executesql @Var,

OPEN @get_data3

FETCH NEXT FROM @get_data3  INTO @var1,@var2,@var3,@var4 ....etc
     WHILE (@@FETCH_STATUS = 0)  


where am I wrong

any help appreciated  
ganeshniceguyAsked:
Who is Participating?
 
ispalenyCommented:
Cursor is for select only, no dynamic sql. If you want to create a dynamic cursor, you must open it in one batch, that can be dynamic.

declare @Var nvarchar(4000)
set @Var = 'select 1'
declare @get_data3  CURSOR
set @Var = 'set @incursor = CURSOR FOR '+@Var+' OPEN @incursor'
execute sp_executesql @Var,N'@incursor CURSOR OUT',@incursor=@get_data3 OUT
fetch from @get_data3
close @get_data3
deallocate @get_data3
0
 
rafranciscoCommented:
A cursor should not begin with an @ sign.
0
 
ganeshniceguyAuthor Commented:
Can you suggest any work around for this

My requirement is i have a dynamic query which is stored in a table ,
I need to call it in a stored procedure and itrerate it row by row using cusor

thanks
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
JulianvaCommented:
Example of a cursor


declare      @typelink varchar (5),
      @curamount money,
      @FeeA money,
      @FeeB money,
      @FeeC money,
      @szcdvnumber varchar(20),
      @Groupcode varchar(5),
      @dtdateopened datetime
      
/* Create Cursor */      
            
declare Fee cursor for

/* selecting rows from matter and Feevalues table*/
select
      szcdvnumber
,      curamount
,      szfee_A
,      szfee_B
,      szfee_C
,      szfeegroupcode
,      szfeetypelink
,      dtdateopened
      

from matter A
join Feevalues F on A.wfeestructure = F.szfeetypelink

where szfeegroupcode = '110' and dtendtdate is null
--and A.szcdvnumber in (select szcdvnumber from tielegal_fees)

open fee

/* fetching into Variables*/
fetch next from fee into
      @szcdvnumber
,      @curamount
,      @feeA
,      @feeB
,      @feeC
,      @Groupcode
,      @Typelink
,      @dtdateopened


While @@fetch_status = 0
/* inserting into legal_fees table*/
begin

insert into Legal_fees
(
      szCDVNumber
,      Total_Due
,      Amount
,      Date
,      TranCode
,      TranSubCode
)
select
 Szcdvnumber = @szcdvnumber

,Total_Due = @curamount

,Amount = case when @curamount <3000.01 then
        @feeA+(@feeA*14/100)
        
        when @curamount >3000 And @curamount <5000.01 then
        @feeB+(@feeB*14/100)
      
        when  @curamount > 5000.01 And @curamount < 9999999999 then
         @feeC+(@feeC*14/100)
        end


,       dtdateopened = getdate()
,      TranCode = 'CF'
,      TranSubCode = @typelink+'-'+@groupcode+'-'+'1040'


fetch next from fee into
      @szcdvnumber
,      @curamount
,      @feeA
,      @feeB
,      @feeC
,      @Groupcode
,      @Typelink
,      @dtdateopened
end

close fee

deallocate fee
0
 
rafranciscoCommented:
If your query for the Cursor is dynamic, then the contents of your stored procedure will consist of dynamic SQL statements as well.  You cannot have a dynamic SELECT statement in a cursor.
0
 
HilaireCommented:
Another option might be to create a GLOBAL cursor in your dynamic SQL.
Thus the cursor will be visible in the parent scope of execution.

exec ('declare C1 cursor GLOBAL for ' + @sql)
open C1
fetch C1 into .....
while @@fetch_status = 0
begin
  -- do whatever you want
  fetch C1 into .....
end
close C1
deallocate C1

Maybe this method is easier, but I think ispaleny's method with the cursor variable is more elegant, since it uses a regular (as opposed to global) cursor.

Cheers

Hilaire
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.