[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 565
  • 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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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