Execute stored procedure with an Output parameter?

Question

I have a stored procedure that I am trying to test. I am trying to test it through SQL Management Studio. In order to run this test I enter ...

exec my_stored_procedure 'param1Value', 'param2Value'

The final parameter is an output parameter. However, I do not know how to test a stored procedure with output parameters.

How do I run a stored procedure with an output parameter?

Answer 1

Here is the stored procedure

create procedure sp1
(
@id as int,
@name as nvarchar(20) out
)
as
begin
select @name=name from employee where id=@id
end

And here is the way to execute the procedure

 declare @name1 nvarchar(10)
    exec sp1 1,@name1 out
    print @name1

Answer 2

From http://support.microsoft.com/kb/262499

Example:

CREATE PROCEDURE Myproc

@parm varchar(10),
**@parm1OUT varchar(30) OUTPUT**,
**@parm2OUT varchar(30) OUTPUT**
AS
  SELECT @parm1OUT='parm 1' + @parm
 SELECT @parm2OUT='parm 2' + @parm

GO

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)

SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                         @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                  @parm1OUT varchar(30) OUTPUT,
                  @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
GO
DROP PROCEDURE Myproc

Hope this helps!

Answer 3

First, declare the output variable:

DECLARE @MyOutputParameter INT;

Then, execute the stored procedure, and you can do it without parameter's names, like this:

EXEC my_stored_procedure 'param1Value', @MyOutputParameter OUTPUT

or with parameter's names:

EXEC my_stored_procedure @param1 = 'param1Value', @myoutput = @MyOutputParameter OUTPUT

And finally, you can see the output result by doing a SELECT:

SELECT @MyOutputParameter 

Answer 4

Please check below example to get output variable value by executing a stored procedure.

    DECLARE @return_value int,
    @Ouput1 int,
    @Ouput2 int,
    @Ouput3 int

EXEC    @return_value = 'Your Sp Name'
        @Param1 = value1,
        @Ouput1 = @Ouput1 OUTPUT,
        @Ouput2 = @Ouput2 OUTPUT,
        @Ouput3 = @Ouput3 OUTPUT

SELECT  @Ouput1 as N'@Ouput1',
        @Ouput2 as N'@Ouput2',
        @Ouput3 as N'@Ouput3'

Answer 5

you can do this :

declare @rowCount int
exec yourStoredProcedureName @outputparameterspOf = @rowCount output

Answer 6

How about this? It's extremely simplified:

1. The SPROC below has an output parameter of @ParentProductID

2. We want to select the value of the output of @ParentProductID into @MyParentProductID which is declared below.

3. Here's the Code:

   declare @MyParentProductID int
   
   exec p_CheckSplitProduct @ProductId = 4077, @ParentProductID =  @MyParentProductID output
   
   select @MyParentProductID