Home > IT Answers > Microsoft Windows > Nested stored procedures in SQL 2000
Pearl52
0 pts.
 Nested stored procedures in SQL 2000
Desktops, Management, Microsoft Windows, OS, Security, Servers, SQL Server
How can I use results from one stored procedure (result is a record set not a single value) in another stored procedure and assign it to a temp table in the second procedure?

Software/Hardware used:
ASKED: January 4, 2006  5:10 PM
UPDATED: January 4, 2006  5:36 PM
RELATED QUESTIONS
Answer Wiki:
There are several ways, including (1) Use a temp table. Temp tables are for the connection, not the procedure. (2) Use a cursor, passing it from one procedure to the other. --- Sheldon Linker Linker Systems, Inc. www.linkersystems.com sol@linker.com 800-315-1174 +1-949-552-1904
Last Wiki Answer Submitted:  January 4, 2006  5:34 pm  by  SheldonLinker   15 pts.
All Answer Wiki Contributors:  SheldonLinker   15 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Declare a TABLE variable and assign results of 2nd stored procedure to it. See code from BOL.

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
–Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
–Display the result set of the table.
–Note that ModifiedDate reflects the value generated by an
–AFTER UPDATE trigger.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Kevin

KingConan
 0 pts.