在 T-SQL 中结合存储过程和查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2563160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine stored procedure and query in T-SQL
提问by abatishchev
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.
等等。
回答by KM.
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 OPENROWSETto 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 表中执行存储过程，而无需命名和知道所有列的类型。
回答by Cade Roux
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 中 - 不复制。
