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.

Answer

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