SQL Server – Execute Stored Procedure with Output Parameter?

Tag:

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?

Thank you!

4 Answers

  1. arora on Mar 05, 2013

    The easy way is to right-click on the procedure in Sql Server Management Studio(SSMS), select ‘execute stored procedure…” and add values for the input parameters as prompted. SSMS will then generate the code to run the proc in a new query window, and execute it for you. You can study the generated code to see how it is done.

  2. fernando-correia on Mar 05, 2013

    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!

  3. arora on Mar 05, 2013

    you can do this :

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

  4. harry on Mar 05, 2013

    Return val from procedure

    ALTER PROCEDURE testme @input  VARCHAR(10),
                       @output VARCHAR(20) output
AS
  BEGIN
      IF @input >= '1'
        BEGIN
            SET @output = 'i am back';

            RETURN;
        END
  END

DECLARE @get VARCHAR(20);

EXEC testme
  '1',
  @get output

SELECT @get