How to Pass Output Parameter to Stored Procedure in Microsoft SQL Server

watch_later 26 November, 2018

Introduction


In This article, I am going to explain how you can pass an output parameter to stored procedure in Microsoft SQL Server and I also explain how you can use stored procedure with output parameter in MS SQL Server. In my previous article, I explained How to create a dynamic stored procedure in MS SQL Server and also explained How to Create and Execute Parameterise Stored Procedure From Another Stored Procedure.

So, In this article will show you how to create a stored procedure for insert and update records within a table using the output parameter in MS SQL Server.

Today, I got a new request from my client, actually, they said he needs a form and wants to validate that form dynamically from the database side. 

Actually, my client said in any situation if they want to add/change/remove any conditions/validation then they can add/change/remove directly from the database side they didn't want to do any changes from the code side and also wants to display appropriate validation message from database side to end user so even they also can change validation message also from database side without change code of web page. 

So, I have written the store procedure for the archive given requirement and that I wanna share with you.

Requirement


1) Create Stored Procedure For Insert and Update Record Within Table in SQL Server.
2) Add Validation Within Created Stored Procedure In SQL Server.
3) Pass an Output Parameter to Stored Procedure in Microsoft SQL Server
4) Return Validation Message using Output Parameter.

Implementation

USE [DB_MyDatabase]  
  
--CREATED ON 26/11/2018 BY NIKUNJ SATASIYA  
  
CREATE PROCEDURE LKS_UserDetails_Ins -- BL_UserInfo_Ins is Procedure Name  
    @UserName VARCHAR(50) ,  
    @Password VARCHAR(50) ,  
    @FirstName VARCHAR(50) ,  
    @LastName VARCHAR(50) ,  
    @Email VARCHAR(50) ,  
    @Location VARCHAR(50) ,  
    @Created_By VARCHAR(50) ,  
  --  @ReturnValue INT = 0 OUT  
   @ReturnValue VARCHAR(50) = '' OUT  
AS   
    BEGIN  
-- SET NOCOUNT ON added to prevent extra result sets from  
-- interfering with SELECT statements.  
                 
        SET NOCOUNT ON ;  
 
  IF EXISTS (SELECT 1 FROM LKS_UserDetails WHERE UserName = @UserName) 
  BEGIN
   SET @ReturnValue = @UserName + ' is Already Exists, Please Try Different Username.'  
   RETURN
  END
 
  IF EXISTS (SELECT 1 FROM LKS_UserDetails WHERE Email = @Email) 
  BEGIN
   SET @ReturnValue = @Email + ' is Already Exists, Please Try Different Email.'  
   RETURN
  END
 
  --- This is Condition To Check wather User is exists or not if user dose not exist within table then returns validation message else returns different validation message  
  
        IF NOT EXISTS ( SELECT 1 FROM LKS_UserDetails  WHERE   UserName = @UserName AND Email = @Email)   
            BEGIN  
                INSERT  INTO BL_User_Info  
                        ( UserName ,  
                          [Password] ,  
                          FirstName ,  
                          LastName ,  
                          Email ,  
                          Location ,  
                          Created_By  
                        )  
                VALUES  ( @UserName ,  
                          @Password ,  
                          @FirstName ,  
                          @LastName ,  
                          @Email ,  
                          @Location ,  
                          @Created_By  
                        )  
    --Messege for User Successfully Registerd 
                --SET @ReturnValue = 0  
                SET @ReturnValue = @UserName + ' is Registered Successfully'  
            END  
        ELSE   
            BEGIN  
   -------------------------****************************----------------------------
   ------ You can Write Update Statment if You want based on your Requirement 
 
   ------ Hear i just Return if user already exists
   -------------------------****************************----------------------------
 
                --SET @ReturnValue = 1  
                SET @ReturnValue = @UserName + ' is Already Exists'  
            END  
    END  
If you analyzed the above-stored procedure then I have declared the output parameter with the name @ReturnValue VARCHAR(50) ='' OUT as Output parameter and set an appropriate message to declared output parameter after successful insert and update operation. and also check the validation before insert and update the record and set validation message to the output parameter.
Output Parameter Sql Server
Output Parameter SQL Server

Summary


This article explains how to use output parameters in SQL Server and how to check validation dynamically from the database side without change any code. 

If You have such SQL tips/tricks and any new things then you can share them with me, I like to learn new things, and also I will post your article with your credit.

I hope this article will help you to archive your requirement,  If you have any questions and need any help then please you can leave your comments in the comment box.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon