CODEWITHSUNDEEP
sqlstored-procedures
Ghost
Ghost

Reputation: 47

SQL - Use result from a Stored Procedure in another Stored Procedure

I have something like this:

Create Procedure GetStockNumber
@Barcode int

As

Select CodStock from TBL_Stock
Where barcode = @barcode

(Let's say the result of the Select is 8 for example)

OK simple :D

BUTTT! I want to use the result (8) of that SP (GetStockNumber) in another SP. Something like this:

Create procedure Blablabla
@Quantity int

As

Update TBL_Stock
Set Quantity = Quantity - @Quantity 
Where CodStock = [THE RESULT OF THE SP (GetStockNumber)]

In this case it would be 8 for this example.

So how can I do this?

**

Solved using the 2º method mentioned by Icarus

**

Upvotes: 2

Views: 4688

Answers (1)

competent_tech
competent_tech

Reputation: 44971

You have a couple of different options:

1) Return an output parameter from the first stored procedure.

Create Procedure GetStockNumber
@Barcode int ,
@CodStock int OUTPUT

As

Select @CodStock = CodStock 
from TBL_Stock
Where barcode = @barcode

to use it:

DECLARE @CodStock int

EXEC GetStockNumber @BarCode, @CodStock OUTPUT

Update TBL_Stock
Set Quantity = Quantity - @Quantity 
Where CodStock = @CodStock

2) Convert the stored procedure to a function that returns a value.

CREATE FUNCTION GetCodStock(@BarCode INT) RETURNS INT
AS
 BEGIN
   RETURN (SELECT CodStock
             FROM TBL_Stock
            Where barcode = @barcode)
 END

To use it:

Update TBL_Stock
Set Quantity = Quantity - @Quantity 
Where CodStock = dbo.GetCodStock(@BarCode)

Upvotes: 4

Related Questions