How to use a value from one stored procedure in another?

I have the following statement in a stored procedure:

DECLARE @Count INT
EXEC @Count = GetItemCount 123
SELECT @Count

Which calls another stored procedure with the following statement inside:

SELECT COUNT(Item) 
FROM tblItem 
WHERE ID = @ID

However when I test the call the EXEC outputs the value correctly but it is not assigned to the @Count parameter correctly.

I've seen examples or stored procedures used like this, including here but none had a parameter and a return value used (that I could find).

The ID parameter is passed into the second statement which returns a count value used by the first stored procedure - all the info I have read seems to indicate this should work - but it doesn't the @Count value is always zero, even when the GetItemCount returns always the correct value.

This is in Microsoft SQL Server 2008 if that helps.

Edit Report

1 Answer

The solution (b) given by Mr. Matthew will not work when u call this in an another stored procedure (Ofcourse solution (a) works perfectly when we use OUTPUT param). Alternation for the solution (b) is Mr.Madhivanan's solution. i.e, create a temp table and use it then drop it.

Below are other solutions..

We cannot get the value from an internal OUTPUT clause in a stored procedure directly. So we have to use OUTPUT parameter or RETURN VALUE instead.

Please refer to the following suggestions:

SOLUTION 1:

CREATE PROCEDURE [dbo].[InsertProduct] 
      @pName varchar(50) 
AS 


BEGIN 
DECLARE @MyTableVar Table(ProductID 
int) 


      INSERT Products 
      ( 
           pName 
      ) 
      OUTPUT Inserted.ProductID 
INTO @MyTableVar 
      VALUES 
      ( 
        @pName 
      ) 
RETURN (SELECT ProductID
FROM @MyTableVar) 
END 


DECLARE @ProductID int 
EXEC @ProductID = [dbo].insertproduc 'TEST' 
SELECT @ProductID

SOLUTION:2

CREATE PROCEDURE [dbo].[InsertProduct]
      @pName varchar(50) , @pID int output
AS

BEGIN 
DECLARE @MyTableVar Table(ProductID int)

      INSERT Products 
      (
           pName 
      )
      OUTPUT Inserted.ProductID INTO @MyTableVar
      VALUES 
      (
        @pName
      )
SELECT @pID=ProductID FROM @MyTableVar
END

DECLARE @ProductID int
EXEC [dbo].insertproduc 'TEST', @ProductID OUTPUT
SELECT @ProductID

answered 2012-05-02T10:25:44.713

How to use a value from one stored procedure in another?

1 Answer

Your Answer