Menu
I'm trying to put the results of a stored procedure into a cursor to use within the current procedure. I've added my code below but I'm not sure if this is possible or if my syntax is correct?
DECLARE cursorIDList CURSOR FOR
EXEC spGetUserIDs
OPEN cursorIDList
FETCH NEXT FROM cursorIDList INTO @ID
I receive the following error: Incorrect syntax near 'EXEC'. Expecting SELECT, '(' or WITH.
Thanks in advance.
207
SQL Server select from stored procedure with parameters 1 First, create a stored procedure that uses multiple parameters to execute some task and return the result. 2 Next, store the result returned by a stored procedure in a table variable. 3 In the end, use the SELECT statement to fetch some data from the table variable. More ...
You could drop the results from the stored proc into a temp table and select from that for your cursor. Another option may be to convert your stored procedure into a table valued function. You use INSERT ... EXEC to push the result of the procedure into a table (can be a temp #table or a @table variable), the you open the cursor over this table.
Copying a SQL Server Stored Procedure’s Results Set to a Local Temp Table You can copy the results set from a stored procedure to a local temp table in a three-step process. In the first step, create a fresh copy of the stored procedure with a select statement that generates a results set whose output you want to persist.
Need Help? Our Support Team is here to help. Here Mudassar Ahmed Khan has provided a tutorial with simple example that explains how to use Cursor in SQL Server Stored Procedures. Cursor is supported in all SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012 and 2014.
You can do it like this:
DECLARE @t TABLE (ID INT)
INSERT INTO @t
EXEC spGetUserIDs
DECLARE cursorIDList CURSOR FOR
SELECT * FROM @t
OPEN cursorIDList
FETCH NEXT FROM cursorIDList INTO @ID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
