Use the result of Stored Procedure as column in SELECT statement

Question

I have a sample Store Procedure:

dbo.spSampleProcedure
 @ID INT,
 @Country VARCHAR(50)

This SP will return a MONEY value(1 column only).

Now I want to implement it this way:

 SELECT 
    c.ID,
    Amount = EXEC spSampleProcedure @ID = c.ID, @Country = c.Country
 FROM Customer c

IS THIS POSSIBLE?

See [here](https://stackoverflow.com/questions/14506871/how-to-execute-a-stored-procedure-inside-a-select-query) — Peter Smith, Oct 08 '18 at 07:36
@PeterSmith oh sorry! so it is not possible coz i wanna use a stored procedure because other dev is maintaining it and always updating. Just for re-usability. Thanks! — Francis Lim, Oct 08 '18 at 07:44

score 0 · Accepted Answer · answered Oct 08 '18 at 07:39

You should consider NOT naming your stored procedures using sp_ as prefix. That is a system reserved prefix and it might cause performance issues. You can read more about it on this article by Aaron Bertrand.

In order to do what you need you could first create a user defined function (as mentioned to you in the comments) that would do the following:

create function myfunc(@Id int, @Country varchar(50))
returns money
as
begin

declare @amount money 

select @amount = amount
from Customer 
where Id = @Id and Country = @Country

return @amount
end

Only then you can try what you wanted to do:

select  c.id,
        amount = dbo.myfunc (c.id, c.country)
from customer c

Thanks! now I know that it is impossible in stored procedure way because i wanna use the SP for maintenance and re-usability. — Francis Lim, Oct 08 '18 at 07:46

Use the result of Stored Procedure as column in SELECT statement

1 Answers1