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

I'm using output parameter in SQL Proc and later I used this values in resultset.

Answer 2

Procedure Example :

Create Procedure [dbo].[test]
@Name varchar(100),
@ID int Output   
As  
Begin   
SELECT @ID = UserID from tbl_UserMaster where  Name = @Name   
Return;
END     

How to call this procedure

Declare @ID int    
EXECUTE [dbo].[test] 'Abhishek',@ID OUTPUT   
PRINT @ID

Answer 3

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.

Answer 4

Check this, Where first two parameters are input parameters and 3rd one is Output parameter in Procedure definition.

DECLARE @PK_Code INT;
EXEC USP_Validate_Login  'ID', 'PWD', @PK_Code OUTPUT
SELECT @PK_Code

Answer 5

CREATE PROCEDURE DBO.MY_STORED_PROCEDURE
(@PARAM1VALUE INT,
@PARAM2VALUE INT,
@OUTPARAM VARCHAR(20) OUT)
AS 
BEGIN
SELECT * FROM DBO.PARAMTABLENAME WHERE PARAM1VALUE=@PARAM1VALUE
END

DECLARE @OUTPARAM2 VARCHAR(20)
EXEC DBO.MY_STORED_PROCEDURE 1,@OUTPARAM2 OUT
PRINT @OUTPARAM2

Answer 6

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