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.

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

Another way

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