How to execute a stored procedure within a SELECT clause in SQL Server

Question

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.

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.

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

No, you cannot do this. It should be a scalar function or dynamic sql. — Ivan Starostin, Nov 14 '16 at 15:22
I have a very long chain of CTE's where in one of the CTE's I am trying to implement this functionality.... — Teja, Nov 14 '16 at 15:25

TheGameiswar · Answer 1 · 2016-11-15T03:43:37.567

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

--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 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

---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

How to execute a stored procedure within a SELECT clause in SQL Server

1 Answers1