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 Variable 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 StoredProcedure - all the info I have read seems to indicate this should work - but it doesn't the @Count value is aways zero, even when the GetItemCount returns always the correct value.

This is in Microsoft SQL Server 2008 if that helps.

Answer 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

Answer 2

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

Answer 3

What you are catching in the variable @Count is the status of execution of the stored procedure GetItemCount and not the value as you are expecting. To capture that value, you have two ways.

1. To declare another parameter @count as OUTPUT in GetItemCount. So you will have GetItemCount declaration as follows

   CREATE PROCEDURE GetItemCount @ID int, @count int output

inside, you can use

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

1. To declare a table before calling the proc and getting that value as a column.

Answer 4

Another way

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