Below I have a procedure that runs a query and returns a cursor. Instead of the query I would like to call another stored procedure that would return a query ( in other words I want to encapsulate the query in an SP). Does anybody know how to accomplish this without having the called query return a cursor
alter PROCEDURE dbo.b700_test_cur (
@myCursorCURSOR VARYING OUTPUT)
as
SET @myCursor= CURSOR
LOCAL
FORWARD_ONLY STATIC
FOR
SELECT * from mytable -- (I want this to be an SP)
OPEN @myCursor
return
Returning a cursor from a stored procedure
Tallman
Hi gwt,
if you want to give a resultset (and the procedure is returning a resultset, just execute the Stored procedure and capture the resultset in a tempory table.
CREATE TABLE #Sometable (<columnlist>)
INSERT INTO #Sometable
EXEC('StoredProcedure')
HTH, jens Suessmeyer.
DRV
When I try that I get this errror:
Server: Msg 197, Level 15, State 1, Procedure b700_test2, Line 62
EXECUTE cannot be used as a source when inserting into a table variable.
For further clarification I have a lot of stored procedures that return resultsets. These stored procedures contain the business rules applicable to each table so I would rather not rewrite them. The problem is that I need to return cursors to my application, so I am trying to create a stored procedure that would take the resultset from my existing stored procedures and return a cursor instead of a resultset.
Thanks for your help.