Posted by Erland Sommarskog on 10/02/23 11:54

(ILCSP@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]