How do I combine executing of a stored procedure and using its result or parameters in a regular SQL query?
For example I would like to do something like the following:
-- passing result of SELECT to SP
SELECT a, b FROM t
EXEC my_sp a, b
-- passing result of SP to INSERT
INSERT INTO t
EXEC my_sp a, b
etc.
Use the sql server "Generate Script" Wizard. Click Next on the "Introduction" window and in the 2nd screen select the option button "Specific Database objects" and click the combo box near "Stored Procedure" (If you are only taking the scripts of stored procedures.
no, you need to use a temp table
create table #results (col1 int, col2 varchar(5) ...)
INSERT INTO #results
EXEC YourProcedure @parma...
then you can join to it
SELECT
*
FROM YourTable y
JOIN #results r ON ...
....
if you don't know the columns and data types from the procedure you can use this excellent answer: Insert results of a stored procedure into a temporary table
In brief it uses OPENROWSET
to execute the stored procedure into a #temp table that is created on the fly, without the need to name and know the type all the columns.
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