asked on

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

rafrancisco

A cursor should not begin with an @ sign.

ganeshniceguy

ASKER

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

Julianva

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

rafrancisco

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.

ASKER CERTIFIED SOLUTION

ispaleny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hilaire

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