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 appreciatedAh, in a word, No.
BUT!
Use a passthru query and do 
EXEC procedurename;|||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.|||I figured it out.  I had to do was create the function below:
CREATE FUNCTION dbo.udfMonthlyIncome
(
@.salary int
,@.frequency int
)  
RETURNS int AS  
BEGIN 
Return
(
 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
 ELSE 0
 END as Income
)
END
Then I can call the function like this:
Select dblSalary, dbo.udfMonthlyIncome(dblSalary,lngFrequency) as MonthlyIncome 
from tblIncome
Thanks for the help guys|||Way to go!
 
No comments:
Post a Comment