SQL Server - Execute Stored Procedure with Output Parameter?

Question

Hello,

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!

Answer 1

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 2

The easy way is to right-click on the procedure in 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.