June 5, 2002 at 1:51 pm
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
June 5, 2002 at 2:10 pm
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
June 5, 2002 at 3:05 pm
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