在T-SQL中组合存储过程和查询

时间:2021-12-17 12:43:41

How do I combine executing of a stored procedure and using its result or parameters in a regular SQL query?

如何组合执行存储过程并在常规SQL查询中使用其结果或参数?

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.

等等

2 个解决方案

#1


24  

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.

简而言之,它使用OPENROWSET将存储过程执行到即时创建的#temp表中,而无需命名并知道所有列的类型。

#2


4  

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.

如果您的SP可以重写为值为UDF的内联表,则这些通常表现非常好并且等同于参数化视图。 ITVF可用于您使用表格或视图的任何地方。

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.

如果您的SP不能作为内联TVF(需要本地变量操作),它可以作为多语句TVF(包含BEGIN / END),根据您的操作可能会或可能不会表现不佳。

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.

在将SP转换为UDF之后,您仍然可以从SP(SELECT * FROM udf(params))或其他地方调用UDF,它可以用于连接等,因此所有代码都在UDF内部 - 否复制。

#1


24  

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.

简而言之,它使用OPENROWSET将存储过程执行到即时创建的#temp表中,而无需命名并知道所有列的类型。

#2


4  

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.

如果您的SP可以重写为值为UDF的内联表,则这些通常表现非常好并且等同于参数化视图。 ITVF可用于您使用表格或视图的任何地方。

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.

如果您的SP不能作为内联TVF(需要本地变量操作),它可以作为多语句TVF(包含BEGIN / END),根据您的操作可能会或可能不会表现不佳。

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.

在将SP转换为UDF之后,您仍然可以从SP(SELECT * FROM udf(params))或其他地方调用UDF,它可以用于连接等,因此所有代码都在UDF内部 - 否复制。