Returning Data Using SQL Output Parameter

Jump To Right Section Show

In the following lesson, you can learn how to Returning Data Using SQL Output Parameter and execution process with an example.

If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. To save the value of the parameter is a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure. For more information about what data types can be used as SQL output parameters.

Examples of SQL Output Parameter

The following example shows a procedure with an input and an output parameter. The @Name parameter would receive an input value specified by the calling program. The SELECT statement uses the value passed into the input parameter to obtain the correct ID value. The SELECT statement also assigns the value to the @ID output parameter, which returns the value to the calling program when the procedure exits.


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

Create Procedure [dbo].[test]

@Name varchar(100),

@ID int Output

Begin

SELECT @ID = UserID from tbl_UserMaster where Name = @Name

Return;

END

How to Execute this Procedure


Declare @ID int    
EXECUTE [dbo].[text] 'TechAid24',@ID OUTPUT   
PRINT @ID

Declare @ID int

EXECUTE [dbo].[text] 'TechAid24',@ID OUTPUT

PRINT @ID

Returning Data Using a Return Code

A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement. As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc, such as:


DECLARE @result int;  
EXECUTE @result = my_proc;

DECLARE @result int;

EXECUTE @result = my_proc;

Execute multiple SQL output Parameters


CREATE PROCEDURE [endicia].[credentialLookup]
@accountNumber varchar(20),
@login varchar(20) output,
@password varchar(50) output
AS
BEGIN
SET NOCOUNT ON;
SELECT top 1 @login = [carrierLogin],@password = [carrierPassword]
  FROM [carrier_account] where carrierLogin = @accountNumber
  order by clientId, id
END

-- 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