如何在SQL Server中的SELECT子句中执行存储过程[duplicate]

This question already has an answer here:

这个问题已经有了答案:

SQL Server - SELECT FROM stored procedure 15 answers
SQL Server——从存储过程中选择15个答案

I am having a scenario like this. I need to execute a stored procedure within a SELECT statement where the input parameters to the calling stored procedure are taken from the table being used in the SELECT statement. Also, there are multiple function calls as below which use the input from table used in SELECT clause but for some reason it doesn't work. Here is my code example as below which I am trying to implement.

我有一个这样的场景。我需要在SELECT语句中执行一个存储过程，其中调用存储过程的输入参数来自SELECT语句中使用的表。此外，还有多个函数调用，它们使用SELECT子句中使用的表的输入，但由于某些原因，它不起作用。下面是我要实现的代码示例。

Code Sample :-

代码示例:

SELECT A,
       B,
       dbo.storedproc( function1(A,B),
                       function1(A,B),
                       function2(B,C),
                       D
                      ) AS newcol,               
       D
 FROM Table;

Error :-

错误:-

Msg 4121, Level 16, State 1, Procedure XYZ, Line 34

Cannot find either column "schemaname" or the user-defined function or aggregate "schemaname.dbo.storedproc", or the name is ambiguous.

Msg 4121，级别16，状态1，过程XYZ，第34行不能找到列“schemaname”或用户定义的函数或聚合“schemaname.dbo”。“storedproc”，或者名称是不明确的。

Note :- I am calling this stored proc for all the rows in the input table by using a set-based approach.

注意:-我使用基于集合的方法为输入表中的所有行调用这个存储的proc。

1 个解决方案

#1

you can't execute stored procedure in select..What you are looking for is a Scalar valued function/table valued function like below

不能在select中执行存储过程。您正在寻找的是一个标量值函数/表值函数，如下所示!

--scalar valued function
    create function dbo.test( int a)
    returns int
    as
    begin
    return someval of int
    end

now you can call above in select like below

现在您可以在select中调用上面的内容，如下所示

select a,b,dbo.test(a) from t

if your stored proc returns mutiple values ,you can create table valued function and can use cross/outer apply

如果存储的proc返回多个值，则可以创建表值函数，并可以使用cross/outer apply

---table valued function example

——表值函数的例子

CREATE FUNCTION dbo.ivf
     ( @a int,
       @b int )
RETURNS table
AS
RETURN (
        SELECT a,b from table
       )

Now you can call this like below

现在你可以这样称呼它

select a,b from table t1
cross apply
(select * from dbo.ivf(a,b))c

#1