Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

Can I pass a string variable to Cursor?

I have a stored procedure as below.  I’d like to pass "cursor scroll dynamic for select Name from Table1 order by Name asc" as varible.  My code used to be like this "set @cursor = cursor scroll dynamic for select Name from Table1 order by Name asc", after I change to "set @cursor =  @SQL", it doesn’t work.

My @SQL = "cursor scroll dynamic for select Name from Table1 order by Name asc".

CREATE PROCEDURE uspCursor
--@SQL varchar(2000),
@Count int,
@StartPoint int,
@Chunk int,
@ReturnIndex int

AS

declare @Cursor     Cursor
declare @Name varchar(50)

set @cursor = cursor scroll dynamic for select Name from Table1 order by Name asc
--set @cursor =  @SQL
open @cursor
fetch absolute @StartPoint from @cursor into @Name

while (@@fetch_status = 0 and @ReturnIndex = 1) or
      (@@fetch_status = 0 and @ReturnIndex = 0 and @Count < @Chunk)
begin
     print 'Name: ' + cast(@Name as varchar(19))
     
     
     set @Count = @Count + 1

     if (@ReturnIndex = 1)
          fetch relative @Chunk from @cursor into @Name
     else
          fetch next from @cursor into @Name
end
close @cursor
0
meimeius
Asked:
meimeius
1 Solution
 
Syed Irtaza AliLead Software ArchitectCommented:
the reason is that
The fetch type Absolute cannot be used with dynamic cursors.

0
 
Brendt HessSenior DBACommented:
You can do this with a Global cursor and an EXEC statement, something like this:

Exec('Declare MyCursor cursor GLOBAL scroll dynamic for select Name from Table1 order by Name asc')

You can now work with the cursor MyCursor:

Open Global MyCursor

fetch absolute @StartPoint from MyCursor into @Name

while (@@fetch_status = 0 and @ReturnIndex = 1) or
     (@@fetch_status = 0 and @ReturnIndex = 0 and @Count < @Chunk)
begin
    print 'Name: ' + cast(@Name as varchar(19))
   
   
    set @Count = @Count + 1

    if (@ReturnIndex = 1)
         fetch relative @Chunk from MyCursor into @Name
    else
         fetch next from MyCursor into @Name
end
close GLOBAL MyCursor
Deallocate Global MyCursor
0
 
meimeiusAuthor Commented:
Thank you very much!
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now