January 27, 2005 at 12:54 pm
Is it possible to exec a stored proc and have the result set of stored proc used to populate a cursor in another stored proc instead of setting the cursor to a select statement?
Example:
DECLARE _cursor CURSOR FOR
(exec Sp_ here instead of select statement)
SELECT lname FROM authors
WHERE lname LIKE "B%"
January 27, 2005 at 1:16 pm
From BOL: (Declare cursor)
Describing the select statement of a cursor:
select_statement
Is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.
Microsoft® SQL Server™ implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Implicit Cursor Conversions.
****
As far as I know it is not possible to use an exe sp instead of a standard select statement.
Quand on parle du loup, on en voit la queue
January 27, 2005 at 2:45 pm
You could also direct the output of the EXEC usp_Myproc to a temp table, then cursor your way through it:
CREATE TABLE ##MyTable ( lname varchar(20), fname varchar(15) ...)
INSERT INTO ##MyTable
EXEC dbo.usp_Myproc
DECLARE mycursor CURSOR FOR
SELECT lname FROM ##MyTable
WHERE lname LIKE "B%"
Lots of overhead this way, what with the cursor and the temp table. Or maybe figure out a way to do without the cursor.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply