April 22, 2003 at 7:54 am
Hiya,
I'm converting some PL/SQL to T-SQL. Rewriting the code that calls the stored procedures is not an option at this time 😉
One of the stored procedures returns a cursor.
The stored procedure is passed a variable 'where' and 'order by' clause.
I can build the sql statement for the cursor creation, but I cannot find an example where a cursor is called with a variable FOR statement.
an example is probably easier to explain:
set @vcSQL = "Select blah from foo"
IF LEN(@p_vcWHERE) <> 0
SET @vcSQL = @vcSQL + ' WHERE ' + @p_vcWHERE
SET @cResultSet = CURSOR LOCAL FOR @vcSQL
If I wasn't trying to create a cursor
sp_executesql @vcSQL
should work.
Is this impossible to do with T-SQL? If so, does anyone know a work-around?
April 22, 2003 at 8:06 am
Try this:
create procedure [proc1] @cursor_name cursor varying output
as
declare @string nvarchar(1000)
set @string = 'declare cursor_name cursor for
select blah from foo ' + char(13)
if len(@p_vcwhere) <> 0 set @string = @string + ' where ' + @p_vcWHERE
exec sp_executesql @string
This will return a cursor.
Jeremy.
April 23, 2003 at 4:57 am
thanks 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply