467,088 Members | 1,299 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,088 developers. It's quick & easy.

Inserting a record using values from another Stored Procedure

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)
AS
SET NOCOUNT ON

SELECT OType, Status, SSN, FName, LName
FROM Customers
WHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)
GO.
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
is:

CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)
AS
SET NOCOUNT ON

-- select the record
SELECT OrderID, OrderDate, SSN
FROM Orders
GROUP BY OrderID, OrderDate, SSN
HAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)
GO.

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
procedure.
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.

Aug 1 '06 #1
  • viewed: 2156
Share:
1 Reply
(IL***@NETZERO.NET) writes:
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)
AS
SET NOCOUNT ON

SELECT OType, Status, SSN, FName, LName
FROM Customers
WHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)
GO.
The value of @@SSnum appears entirely pointless.
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?
Yes, this is trivial.

SELECT @Otype = Otype, @Status = Status,

etc, and then use these variables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 2 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Chad Dittmer via .NET 247 | last post: by
3 posts views Thread by TheDude5B | last post: by
4 posts views Thread by thebison | last post: by
15 posts views Thread by Jaraba | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.