How To Pass Output Parameter To Stored Procedure In SQL Server

In this tutorial, I will explain how to pass an output parameter to a stored procedure in MS SQL Server and also, we will see how to use stored procedure in SQL Server with an output parameter.

So, we will write the stored procedure for inserting the data for demonstration.
 
Stored Procedure in SQL Server
  1. USE [DB_MANTRY]  
  2.   
  3. --CREATED ON 08/12/2017 BY NIKUNJ SATASIYA  
  4.   
  5. CREATE PROCEDURE BL_UserInfo_Ins -- BL_UserInfo_Ins is Procedure Name  
  6.     @UserName VARCHAR(50) ,  
  7.     @Password VARCHAR(50) ,  
  8.     @FirstName VARCHAR(50) ,  
  9.     @LastName VARCHAR(50) ,  
  10.     @Email VARCHAR(50) ,  
  11.     @Location VARCHAR(50) ,  
  12.     @Created_By VARCHAR(50) ,  
  13.     @ReturnValue INT = 0 OUT  
  14. AS   
  15.     BEGIN  
  16. -- SET NOCOUNT ON added to prevent extra result sets from  
  17. -- interfering with SELECT statements.  
  18.                  
  19.         SET NOCOUNT ON ;  
  20. ---Condition For Check if User exists or not if user does not exist then returns different message if exists returns different message  
  21.   
  22.         IF NOT EXISTS ( SELECT  * FROM    BL_User_Info  WHERE   UserName = @UserName )   
  23.             BEGIN  
  24.                 INSERT  INTO BL_User_Info  
  25.                         ( UserName ,  
  26.                           [Password] ,  
  27.                           FirstName ,  
  28.                           LastName ,  
  29.                           Email ,  
  30.                           Location ,  
  31.                           Created_By  
  32.                         )  
  33.                 VALUES  ( @UserName ,  
  34.                           @Password ,  
  35.                           @FirstName ,  
  36.                           @LastName ,  
  37.                           @Email ,  
  38.                           @Location ,  
  39.                           @Created_By  
  40.                         )  
  41. --If User Successfully Registerd then we will return this Message as Output Parameter  
  42.                 --SET @ReturnValue = 0  
  43.                 SET @ReturnValue = @UserName + ' is Registered Successfully'  
  44.             END  
  45.         ELSE   
  46.             BEGIN  
  47. --If User already Exists We will return this Message as Output Parameter  
  48.                 --SET @ReturnValue = 1  
  49.                 SET @ReturnValue = @UserName + ' is Already Exists'  
  50.             END  
  51.     END  
You can see the created stored procedure where we are sending @ReturnValue as an output parameter. And, it shows the appropriate message to the user based on the return value.

Codingvila
Codingvila is an educational website, developed to help tech specialists/beginners.