Using a Stored Procedure in a Pass-Through

[AdamFeerst]

Why isn't this working?
SELECT usp_BillerStatus.* FROM usp_BillerStatus

Error message:
ODBC--call failed
Invalid object name 'usp_BillerStatus'. #208.

However, Execute usp_BillerStatus does work.

These are pass-through queries from Access2013 running on a SQL server 2005

 

[pbaldy]

I don't think you can call a stored procedure with a SELECT. I use them in pass through queries like:

EXEC ProcName

 

[AdamFeerst]

The bigger/real issue is that I want to refer to the stored procedure in another pass-through.

WITH BillerIDProd as (SELECT tblBillerList.*, tblBillerIDProd.PROD, tblBillerIDProd.Status AS 'PRDSTS' FROM tblBillerList INNER JOIN tblBillerIDProd ON tblBillerList.ClientID = tblBillerIDProd.ClientID) SELECT BillerIDProd.*, usp_BillerStatus.Status as 'UStatus' FROM BillerIDProd LEFT JOIN usp_BillerStatus ON BillerIDProd.ClientID = usp_BillerStatus.AcctID

 

[pbaldy]

I don't think you can do that. I had a similar need and changed the SP to a table valued function. There are a lot of thoughts here:

http://www.sommarskog.se/share_data.html

referenced from here:

http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure

 

[Galaxiom]

A stored procedure cannot be used in a Select. They can only be executed.

One way use a stored procedure in a Select is to wrap it in a table valued function. The function can be used as a table in a Select query.

 

[AdamFeerst]

Thanks. We're either going to use Table Valued Functions or,
Build them entirely as stored procedures, create Execute pass-throughs, then use those as record sources for the forms and reports.

I'm writing all the SQL code in Access for now. Then the SQL dba (not me) will be able to modify them to work as TVFs or SPs. I have more band width than he does.