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  maloy Starting Member 19 Posts Posted - 2001-10-29 : 09:52:57 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