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.
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 your SP can be rewritten as an inline table valued UDF, these typically perform very well and are equivalent to a parametrized view. ITVF can be used any place you would use a table or view.
If your SP won't work as an inline TVF (local variable manipulation required), it may work as a multi-statement TVF (contains a BEGIN/END) which may or may not perform poorly depending on what you have to do.
After your SP has been turned into a UDF, you can then still call the UDF from your SP (SELECT* FROM udf(params)) or elsewhere it can be used for joins, etc, so all your code is inside the UDF - no duplication.
