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

Question

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.

Solution 1

In your stored procedure, are you either

a) Assigning the value of the count to an output parameter:

CREATE PROCEDURE GetItemCount
  @id INT,
  @count INT OUTPUT
AS
  SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @id

called as:

DECLARE @count INT
EXEC GetItemCount 123, @count OUTPUT

or, b) Assigning the count value as the return value:

CREATE PROCEDURE GetItemCount
  @id INT
AS
BEGIN
  DECLARE @count INT
  SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @id

  RETURN @count
END  

called as:

DECLARE @count INT
EXEC @count = GetItemCount 123

Solution 2

Another way

DECLARE @Count table(counting INT)
Insert into @Count
EXEC GetItemCount 123 
SELECT Counting FROM @Count 

Solution 3

The following will not work if there are no input parameters for the executed SP:

EXEC [dbo].insertproduc 'TEST', @ProductID OUTPUT --works
EXEC [dbo].insertproduc, @ProductID OUTPUT -- generates a parameter supplied when not needed error message if the insertproduc does not require parameters.

Best to use the following format for all cases (it works on all versions):

DECLARE @MyOutputVariable int 
EXEC @MyOutputVariable  = [dbo].MyStoredProc 

Solution 4

You should pass @Count as an output parameter.

Create Proc dbo.usp_Proc1

@Id int,
@Count int output

as begin

select @Count = Count(Item) from tblItem where id=@Id

end
Go

Declare @Count int
Declare @Id int

Set @Id = 1

Exec dbo.usp_Proc1 @Id, @Count output

select @Count

Solution 5

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

View more solutions

Comments

[RoguePlanetoid]

I was able to get this to work - having an output as a parameter was the solution - keep forgetting SQL is not like the programming style I'm used to.

[RoguePlanetoid]

Thanks for this - it worked - did not think that an output would be a parameter - that's why I could not figure it out I was treating the display output as an output when in fact nothing was being returned - hence why it was zero when I read the value.

[RoguePlanetoid]

This example was also helpful, to getting my query to work correctly so thanks again.

[RoguePlanetoid]

Have searched for this issue a few times since I asked this question and came back here and found the solution, wish could up vote each time it's helped me!

[Madhivanan]

@MortezaFarhadi It will work in 2008 as well. Did you get any error?