?
Solved

CURSOR FOR EXECUTE problem

Posted on 2005-04-12
6
Medium Priority
?
2,244 Views
Last Modified: 2010-03-15
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  
0
Comment
Question by:ganeshniceguy
6 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13761936
A cursor should not begin with an @ sign.
0
 

Author Comment

by:ganeshniceguy
ID: 13762005
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
 
LVL 8

Expert Comment

by:Julianva
ID: 13762025
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13762034
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
 
LVL 13

Accepted Solution

by:
ispaleny earned 750 total points
ID: 13762079
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 13762215
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question