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?
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
user defined function