Reference a result set from a stored proc

Question

Reference a result set from a stored proc

Sean Grebey-262535

SSCommitted

Points: 1724
More actions
September 21, 2005 at 11:36 am

#165618

Sorry if this is a noob question. But how do I reference a result recordset from a Stored Procedure that runs a select statement.
So if my Stored Proc is
CREATE PROCEDURE MyStoredProc AS
SELECT * FROM MyTable
Return
After I do an:
Exec MyStoredProc
How can I reference that recordset? Like for example can I do a join on that returned recordset and a table to get a thrid recordset?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can't (read shouldn't) do that. Move that select into a view or a inline table function if you need parameters. Then you'll be able to do the join.

Also, have you thaught about simply creating an all new select for the task at hand (hard to say if it's the best choice here without all the info).

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 2

Well I'm trying to convert this crazy nested Access mess somebody had written into SQL Server. So I should use Views instead of seperate StoredProcs?

(qryDLShowAllEmp)

SELECT IIf([Emp_Status]="Active",Yes,No) AS Active, tlkpEmpeeDetail.Emp_Status, tblEmpName.Emp_ID, [tlkpEmpeeDetail]![Emp_LastName] & ", " & [tlkpEmpeeDetail]![Emp_FirstName] & " " & [tlkpEmpeeDetail]![Emp_MiddleInit] AS FullName, tlkpEmpeeDetail.Emp_Dist, tlkpEmpeeDetail.Emp_Dept, tblDLEmpList.Vacation, tblDLEmpList.OnCall, tblDLEmpList.CanCall, tblDLEmpList.BList, tlkpEmpeeDetail.Emp_ClassName, tblDLEmpList.CrewStatusOnly

FROM (tblEmpName LEFT JOIN tblDLEmpList ON tblEmpName.Emp_ID = tblDLEmpList.Emp_ID) INNER JOIN tlkpEmpeeDetail ON tblEmpName.Emp_ID = tlkpEmpeeDetail.Emp_ID

ORDER BY [tlkpEmpeeDetail]![Emp_LastName] & ", " & [tlkpEmpeeDetail]![Emp_FirstName] & " " & [tlkpEmpeeDetail]![Emp_MiddleInit];

(qryDLEmpOnCall)

SELECT tblEmpName.Emp_ID, tlkpEmpeeDetail.Emp_Dist, tlkpEmpeeDetail.Emp_Dept, tlkpEmpeeDetail.Emp_ClassName, qryDLShowAllEmp.FullName, tlkpEmpeeDetail.Emp_Phone1, tlkpEmpeeDetail.Emp_Phone2, qryDLShowAllEmp.CanCall, qryDLShowAllEmp.BList, qryDLShowAllEmp.CrewStatusOnly

FROM (tblEmpName LEFT JOIN qryDLShowAllEmp ON tblEmpName.Emp_ID = qryDLShowAllEmp.Emp_ID) INNER JOIN tlkpEmpeeDetail ON tblEmpName.Emp_ID = tlkpEmpeeDetail.Emp_ID

WHERE (((qryDLShowAllEmp.Active)=Yes) AND ((qryDLShowAllEmp.Vacation)=No) AND ((qryDLShowAllEmp.OnCall)=Yes)) OR (((qryDLShowAllEmp.CanCall)=Yes) AND ((qryDLShowAllEmp.Active)=Yes) AND ((qryDLShowAllEmp.Vacation)=Yes) AND ((qryDLShowAllEmp.OnCall)=Yes))

ORDER BY qryDLShowAllEmp.FullName;

(qryDLOnCall1st)

SELECT DISTINCTROW qryDLEmpOnCall.Emp_Dist, qryDLEmpOnCall.Emp_Dept, qryDLEmpOnCall.Emp_ID, qryDLEmpOnCall.FullName, qryDLEmpOnCall.Emp_ClassName, qryDLEmpOnCall.Emp_Phone1, qryDLEmpOnCall.Emp_Phone2, Max(tblCallOuts.CallDate) AS MaxOfCallDate

FROM qryDLEmpOnCall LEFT JOIN tblCallOuts ON qryDLEmpOnCall.Emp_ID = tblCallOuts.Emp_ID

GROUP BY qryDLEmpOnCall.Emp_Dist, qryDLEmpOnCall.Emp_Dept, qryDLEmpOnCall.Emp_ID, qryDLEmpOnCall.FullName, qryDLEmpOnCall.Emp_ClassName, qryDLEmpOnCall.Emp_Phone1, qryDLEmpOnCall.Emp_Phone2

HAVING (((Max(tblCallOuts.CallDate))>=Date()-getDLOnCallAge() Or (Max(tblCallOuts.CallDate)) Is Null))

ORDER BY qryDLEmpOnCall.Emp_Dist, Max(tblCallOuts.CallDate);

(qryDLOnCall2nd)

SELECT qryDLOnCall1st.Emp_Dist, qryDLOnCall1st.Emp_Dept, qryDLOnCall1st.Emp_ID, qryDLOnCall1st.FullName, qryDLOnCall1st.Emp_ClassName, qryDLOnCall1st.Emp_Phone1, qryDLOnCall1st.Emp_Phone2, Max(qryDLOnCall1st.MaxOfCallDate) AS MaxOfMaxOfCallDate, Last(tblCallOuts.CallTime) AS LastOfCallTime

FROM (tblEmpName INNER JOIN qryDLOnCall1st ON tblEmpName.Emp_ID = qryDLOnCall1st.Emp_ID) LEFT JOIN tblCallOuts ON tblEmpName.Emp_ID = tblCallOuts.Emp_ID

GROUP BY qryDLOnCall1st.Emp_Dist, qryDLOnCall1st.Emp_Dept, qryDLOnCall1st.Emp_ID, qryDLOnCall1st.FullName, qryDLOnCall1st.Emp_ClassName, qryDLOnCall1st.Emp_Phone1, qryDLOnCall1st.Emp_Phone2

ORDER BY Max(qryDLOnCall1st.MaxOfCallDate), Last(tblCallOuts.CallTime);

(qryDLOnCall3rd)

INSERT INTO tblDLOnCallTemp ( Division, Department, Employee, Name, EmpClass, PhoneNumber, AltPhoneNumber )

SELECT qryDLOnCall2nd.Emp_Dist, qryDLOnCall2nd.Emp_Dept, qryDLOnCall2nd.Emp_ID, qryDLOnCall2nd.FullName, qryDLOnCall2nd.Emp_ClassName, qryDLOnCall2nd.Emp_Phone1, qryDLOnCall2nd.Emp_Phone2

FROM qryDLOnCall2nd

ORDER BY qryDLOnCall2nd.FullName;