Combine stored procedure and query in T-SQL

Question

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.

Can you rewrite your question? It doesn't make much sense in it's current form. — JohnFx, Apr 01 '10 at 19:42
@JohnFx: I'm asking about all possible combinations of SP and DML. Why it has no sense? — abatishchev, Apr 01 '10 at 19:45
No offense, I just think that the meaning of your question is getting lost in the somewhat poor translation to English. — JohnFx, Apr 01 '10 at 19:49
@JohnFx: It would be very nice if you could rewrite it a bit to increase the sense according to the idea I described above — abatishchev, Apr 01 '10 at 19:51
He's asking a common question: is there a way to use the output of a stored procedure as a subquery. He's also asking the reverse: is there a way to pass the results of a subquery as a parameter to a stored procedure. — egrunin, Apr 02 '10 at 00:17

score 25 · Accepted Answer · edited May 23 '17 at 12:19

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.

score 4 · Answer 2 · answered Apr 01 '10 at 20:46

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.

Combine stored procedure and query in T-SQL

2 Answers2

Linked