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 开发者_如何学Pythonthe 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.

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 

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 

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

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

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
   

2. To declare a table before calling the procedure and getting that value as a column.

Simplifying it after reading all answers. Used two input and two output parameters.

Make sure you assign some values to output params in your stored procedure.

My stored procedure:

CREATE PROCEDURE [dbo].[MyStoredProcedure]
    (@InParam1  VARCHAR(20) = NULL,
     @InParam2  VARCHAR(20) = '',
     @OutParam1 VARCHAR(20) = NULL OUT,
     @OutParam2 VARCHAR(20) = NULL OUT)
AS
BEGIN
    SET @OutParam1 = 'Whatever you want to return'
    SET @OutParam2 = 'Whatever you want to return'
    RETURN 0; --This is final value any SP returns by default 0
END

Call this stored procedure - I used two output params so we need to declare them before executing the stored procedure:

DECLARE @OutParam1 VARCHAR(20)
DECLARE @OutParam2 VARCHAR(20)

Use any variable (ex. @Var) even if you do not finally need from the stored procedure, but this will set your output params without any return data like it does return:

Method 1:

DECLARE @Var INT
EXEC @Var = MyStoredProcedure 'InParam1', 'InParam2', @OutParam1 OUT, @OutParam2 OUT

If you want to return some select statement from your stored procedure, and want to use these values, you can either hold them ia temp table or simply uses as a select query.

Method 2:

Insert into a temp table

@DECLARE @TableTemp TABLE
                    (
                        Column1 VARCHAR(20),
                        Column2 VARCHAR(20)
                    )

INSERT INTO @TableTemp
    EXEC MyStoredProcedure 'InParam1', 'InParam2', @returnParam1 OUT, @returnParam2 OUT

This will set the two output params from MyStoredProcedure and also insert the values that you pass from MyStoredProcedure as a select statement.

Method 3:

To simply use the returned values as a table data we can use this:

EXEC MyStoredProcedure 'InParam1', 'Inparam2', @returnParam1 OUT, @returnParam2 OUT

Output params are always set in all above cases.

You can also try this based on this answer by Madhivanan:

DECLARE @tblItem table(Item INT)
DECLARE @Count INT

Insert into @tblItem 
EXEC GetItemCount 123 
SELECT @Count = Item FROM @tblItem