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

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

Another way

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

Related