CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Andrew
Andrew

Reputation: 477

SQL Server - Execute SP in select

I have a Temp table that I am populating from a stored procedure. Once the data is in the temp table, I am using it to run calculations and populate other fields.

Example:

CREATE TABLE #Result1 (
  SOrder integer,
  Var1 double,
  Var2 double,
  Var3 double,
  Calc1 double,
  Calc2 double )

--Fills the Temp Table initially
Insert #Result1
(Select sorder, var1, var2, var3, 0,0,0 from myTable)


--Fills with calculation stuff
Select SOrder = 0,
       Calc1 = Var1/Var2 * 100
       Calc2 = (Var2-Var1)/Var3 * 100
From #Result1

What I want to be able to do is this:

--Fills with SP stuff
Select SOrder = 0,
       Calc1 = exec spLocal_CalcMetric (Var1, Var2)
       Calc2 = exec spLocal_CalcMetric (Var1, Var2)
From #Result1

Is there any way to do something like this to update the temp table with returns from an SP?

Upvotes: 0

Views: 41

Answers (1)

DavidG
DavidG

Reputation: 118937

You can do it with a function, not a procedure, but all you're doing is complicating a simple select here. Functions can also be a performance issue. However, you can do this:

CREATE FUNCTION dbo.Local_CalcMetric 
(
    @var1 DOUBLE, 
    @var2 DOUBLE, 
    @var3 DOUBLE
)
RETURNS DOUBLE
AS 
BEGIN
    RETURN (@var2-@var1)/@var3 * 100
END

And use it:

SELECT SOrder = 0,
       Calc1 = dbo.Local_CalcMetric (Var1, Var2, Var3)
       Calc2 = dbo.Local_CalcMetric (Var1, Var2, Var3)
FROM #Result1

Upvotes: 2

Related Questions