Menu
Possible Duplicate:
How to SELECT * INTO [temp table] FROM [stored procedure]
I have a nested stored procedure call
In one of the stored procedures I want to save the result into a table variable likt this :
INSERT INTO @myTable
EXEC sp_myStoredProcedure
however, because the proc. is nested the following error occurs : An INSERT EXEC statement cannot be nested
The procedure must be called from another procedure, changing this is not an option. I wanted to try to use an output parameter but it still has to be set with a Insert into statement.
What are other options to save the data that is retrieved from the call of a Stored Procedure into a variable ?
599
You can use the return statement inside a stored procedure to return an integer status code (and only of integer type). By convention a return value of zero is used for success. If no return is explicitly set, then the stored procedure returns zero. You should use the return value for status codes only.
Most stored procedures return multiple result sets. Such a stored procedure usually includes one or more select statements. The consumer needs to consider this inclusion to handle all the result sets.
To save the modifications to the procedure definition, on the Query menu, select Execute. To save the updated procedure definition as a Transact-SQL script, on the File menu, select Save As. Accept the file name or replace it with a new name, and then select Save.
Launch MS SQL Server Management Studio in your system and go to the Object Explorer. Step 2. Right-click on the database from which you want to move data and then click on Tasks>>Generate Scripts… A Generate and Publish Scripts Wizard will appear on the screen, click on the Next button to proceed.
Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables.
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
http://support.microsoft.com/kb/305977/en-us
66
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
