Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
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 procedure in a new query window, and execute it for you. You can study the generated code to see how it is done.
                I know all other possible ways to execute stored procedure(like EXEC , calling from C# or PHP) but this is the easiest and a non technical person can do this. so +1 for this and thx for sharing the information.
– Dhaval
                Sep 3, 2013 at 11:13
                That seems odd. My context menu on a stored procedure has about a dozen items, including modify, execute, properties, and others
– Ray
                Jun 8, 2015 at 14:56
                I think the answer by Jaider below completes this answer since I myself would be interested in the written command and not the mouse solution.
– Alwyn Schoeman
                Dec 6, 2017 at 2:30
                Another slightly different way using SSMS: right-click the SP, click "Script Stored Procedure as", then click "EXECUTE To". This will show you the TSQL.
– John Gilmer
                Dec 17, 2019 at 6:15
Check this, where the first two parameters are input parameters and the 3rd is an Output parameter in the Procedure definition.
DECLARE @PK_Code INT;
EXEC USP_Validate_Login 'ID', 'PWD', @PK_Code OUTPUT
SELECT @PK_Code
From https://learn.microsoft.com/en-US/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql (originally http://support.microsoft.com/kb/262499)
CREATE PROCEDURE Myproc
@parm varchar(10),
**@parm1OUT varchar(30) OUTPUT**,
**@parm2OUT varchar(30) OUTPUT**
  SELECT @parm1OUT='parm 1' + @parm
 SELECT @parm2OUT='parm 2' + @parm
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"
DROP PROCEDURE Myproc
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 
With this query you can execute any stored procedure (with or without an output parameter):
DECLARE @temp varchar(100)  
EXEC my_sp
    @parameter1 = 1, 
    @parameter2 = 2, 
    @parameter3 = @temp output, 
    @parameter4 = 3, 
    @parameter5 = 4
PRINT @temp
Here the datatype of @temp should be the same as @parameter3 within your Stored Procedure.
The SPROC below has an output parameter of @ParentProductID
We want to select the value of the output of @ParentProductID into @MyParentProductID which is declared below. 
Here's the Code:
declare @MyParentProductID int
exec p_CheckSplitProduct @ProductId = 4077, @ParentProductID =  @MyParentProductID output
select @MyParentProductID
Try this; it's working fine for the multiple output parameter:
CREATE PROCEDURE [endicia].[credentialLookup]
@accountNumber varchar(20),
@login varchar(20) output,
@password varchar(50) output
BEGIN
SET NOCOUNT ON;
SELECT top 1 @login = [carrierLogin],@password = [carrierPassword]
  FROM [carrier_account] where carrierLogin = @accountNumber
  order by clientId, id
Try for the result: 
SELECT *FROM [carrier_account] 
DECLARE @login varchar(20),@password varchar(50)
exec [endicia].[credentialLookup] '588251',@login OUTPUT,@password OUTPUT
SELECT 'login'=@login,'password'=@password
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'