Dynamically built cursors??

  • Please help,

    I want to build the DECLARE CURSOR statement dynamically. Can anyone tell me how to do it?

    Example(The SELECT_STATEMENT is a variable passed to the stored procedure:

    DECLARE CURSOR_NAME CURSOR FOR

    SELECT_STATEMENT

  • Never tried it, but I imagine it would have to be dynamic sql so you would have to exec() or sp_executesql the entire construct. Stuff like this is usually done much more easily on the client - just open a recordset with a sql string.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Here is some code that I wrote that you can alter.

    This is for a custom database so ignore what it's doing and concentrate on parts of the structure that you'll need.

    In order to pass a statement to a procedure that will be used as a cursor you'll probably need to have a set number of arguments returned from your query.

    CREATE PROCEDURE FixMissingAccountCodes (@dbname varchar(64))

    AS

    declare @largeisql varchar(8000)

    set @largeisql = 'declare c4 cursor for select b.SUBTYPE_VALUE, b.CODE, a.ColumnName, b.exp_key from eoperationswork..valueids a join ' + @dbname + '..acctcodemgr_codes b on a.VID=b.SUBTYPE_VALUEID where b.SUBTYPE_VALUEID <> 0

    declare @column varchar(128), @code varchar(64), @col_value varchar(128), @exp_key int

    declare @isql varchar(8000)

    open c4

    fetch next from c4 into @col_value,@code,@column,@exp_key

    while @@fetch_status = 0

    begin

    select @isql = ''update ' + @dbname + '..qx_report_entry set rpe_proj = '''''' + @code + '''''' where rpe_proj is null and exp_key = '' + convert(varchar(16),@exp_key) + '' and '' + @column + '' = '''''' + @col_value + ''''''''

    exec (@isql)

    --print @isql

    fetch next from c4 into @col_value,@code,@column,@exp_key

    end

    close c4

    deallocate c4'

    exec (@largeisql)

    return

    GO

    Good Luck,

    DanW

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply