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 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!