|Subject:||Inserting a record using values from another Stored Procedure|
|Date:||1 Aug 2006|
Hello, I'm trying to accomplish 3 things with one stored procedure.
I'm trying to search for a record in table X, use the outcome of that
search to insert another record in table Y and then exec another stored
procedure and use the outcome of that stored procedure to update the
record in table Y.
I have this stored procedure (stA)
CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)
SET NOCOUNT ON
SELECT OType, Status, SSN, FName, LName
WHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)
Then, I need to create a new record in another table (Y) using the SSN,
FName and Lname fields from this stored procedure.
After doing so, I need to run the second stored procedure (stB) Here it
CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)
SET NOCOUNT ON
-- select the record
SELECT OrderID, OrderDate, SSN
GROUP BY OrderID, OrderDate, SSN
HAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)
After running this, I need to update the record I created a moment ago
in table Y with the OrderDate and OrderID from the second stored
Do you guys think that it can be done within a single stored procedure?
Like for example, at the end of store procedure A creating an insert
statement for the new record, and then placing something like exec
procstB 'SSN value'? to run stored procedure B and then having a
update statement to update that new record?
Thanks for all your help.