Unanswered: Calling Stored Procedures in a Select Statement
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
If you are going to try joining into the result set from your stored procedure, this is generally frowned upon (when allowed at all) because a procedure can conceivable return more than one result set, and could even modify data during execution that is also reference by your SQL statement. Confusion results.
If possible, rewrite your stored procedure as a view or as a user-defined table function.
If it's not practically useful, then it's practically useless.
I figured it out. I had to do was create the function below:
CREATE FUNCTION dbo.udfMonthlyIncome
RETURNS int AS
SELECT CASE @frequency
WHEN 1 /*'Bi-Weekly'*/ THEN (@salary * 26) / 12
WHEN 2 /*'Daily'*/ THEN @salary * 20
WHEN 3 /*'Hourly'*/ THEN (@salary * 40 * 52) / 12
WHEN 4 /*'Monthly'*/ THEN @salary
WHEN 5 /*'Quarterly'*/ THEN @salary / 3
WHEN 6 /*'Semi-Annual'*/ THEN @salary / 6
WHEN 7 /*'Semi-Monthly'*/ THEN @salary * 2
WHEN 8 /*'Weekly'*/ THEN (@salary * 52) / 12
WHEN 9 /*'Annual'*/ THEN @salary / 12
END as Income
Then I can call the function like this:
Select dblSalary, dbo.udfMonthlyIncome(dblSalary,lngFrequency) as MonthlyIncome