none
Passing cursor as a parameter to the stored procedure in SQL Server

    Question

  • Hi,

    Is it possible to pass cursor as a parameter to the stored procedure in SQL Server?

    Monday, February 28, 2011 11:37 AM

Answers

  • Hello,

    a SP accepts cursor parameters, but only as output. This means, the cursor has to be declared and opened within the SP, and an "outer" SP can access this cursor. Example:

    create table testtab (code varchar(40)
    insert into testtab('a')
    insert into testtab('b')

    go

    create proc testproc @c cursor varying output
    as begin
    set @c = cursor for select code from testtab
    open @c
    end

    go

    declare @c cursor
    declare @code varchar(40)
    exec testproc @c output
    while (1 = 1) begin
       fetch @c into @code
       if @@fetch_status <> 0 break
       print @code
    end
    close @c

    But this isn't what you want, am I right?

    Regards,

    Klaus

     

    • Proposed as answer by Naomi NModerator Monday, February 28, 2011 2:02 PM
    • Marked as answer by Ai-hua Qiu Tuesday, March 08, 2011 7:49 AM
    Monday, February 28, 2011 1:52 PM

All replies

  • I think you can not, but you can call the Stored Procedure using the current cursor variables

    If you check the SQL Server cursor example here, within the cursor code a stored procedure is called


    SQL Server and T-SQL Tutorials

    Monday, February 28, 2011 11:42 AM
    Moderator
  • Hello,

    a SP accepts cursor parameters, but only as output. This means, the cursor has to be declared and opened within the SP, and an "outer" SP can access this cursor. Example:

    create table testtab (code varchar(40)
    insert into testtab('a')
    insert into testtab('b')

    go

    create proc testproc @c cursor varying output
    as begin
    set @c = cursor for select code from testtab
    open @c
    end

    go

    declare @c cursor
    declare @code varchar(40)
    exec testproc @c output
    while (1 = 1) begin
       fetch @c into @code
       if @@fetch_status <> 0 break
       print @code
    end
    close @c

    But this isn't what you want, am I right?

    Regards,

    Klaus

     

    • Proposed as answer by Naomi NModerator Monday, February 28, 2011 2:02 PM
    • Marked as answer by Ai-hua Qiu Tuesday, March 08, 2011 7:49 AM
    Monday, February 28, 2011 1:52 PM
  • Probably. You can also use rocks to drive screws into wood.

    Why would you even think of such a non-relational, slow approach to SQL?  Instead of telling us HOW you want to do something, ask us WHAT you want to do.  Post DDL, sample data and clear specs.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Monday, February 28, 2011 8:32 PM