Cursor as output parameter for dynamic-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor as output parameter for dynamic-SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-02 : 09:00:40

Maloy writes "I have the following code:
Create procedure proc_return_cursor
(@table_name varchar(50),
@return_cursor cursor varying output)
as
begin
declare @query nvarchar(1000),
@param nvarchar(100)
set @query=N'Select [code], [desc],.other fields..'+
'from '+ @table_name
set @param='@return_cursor cursor output'
execute sp_executesql @query,@param,@return_cursor output
open @return_cursor --removing this line also does not work
return
end

This sp is being called from another sp, which has a local cursor variable.
Exec proc_return_cursor @table_name,@local_cursor_variable output

I get the error no. 16950:"variable does not have a cursor currently allocated to it". both in proc_return_cursor(open cursor statement) as well as in the calling sp.

Therefore Fetch next into variables does not work in the calling sp. I do not want to use a temp table, as I need a cursor for manipulating records. I need to pass the cursor as an output parameter.
Also "declare cursor_name cursor for execute sp_executesql (nvarchar query)" does not work, giving yntax error.

tia
Maloy"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources