SQL Server 2016 Hosting – HostForLIFE.eu :: How To Pass Output Parameter To Stored Procedure In SQL Server?

Peter Cole | January 10, 2018 | SQL 2016 Hosting | No Comments

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

    USE [DB_MANTRY]  
      
    CREATE PROCEDURE BL_UserInfo_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  
    AS   
        BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
                     
            SET NOCOUNT ON ;  
    ---Condition For Check if User exists or not if user does not exist then returns different message if exists returns different message  
      
            IF NOT EXISTS ( SELECT  * FROM    BL_User_Info  WHERE   UserName = @UserName )   
                BEGIN  
                    INSERT  INTO BL_User_Info  
                            ( UserName ,  
                              [Password] ,  
                              FirstName ,  
                              LastName ,  
                              Email ,  
                              Location ,  
                              Created_By  
                            )  
                    VALUES  ( @UserName ,  
                              @Password ,  
                              @FirstName ,  
                              @LastName ,  
                              @Email ,  
                              @Location ,  
                              @Created_By  
                            )  
    --If User Successfully Registerd then we will return this Message as Output Parameter  
                    --SET @ReturnValue = 0  
                    SET @ReturnValue = @UserName + ' is Registered Successfully'  
                END  
            ELSE   
                BEGIN  
    --If User already Exists We will return this Message as Output Parameter  
                    --SET @ReturnValue = 1  
                    SET @ReturnValue = @UserName + ' is Already Exists'  
                END  
        END

USE [DB_MANTRY]

CREATE PROCEDURE BL_UserInfo_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

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON ;

---Condition For Check if User exists or not if user does not exist then returns different message if exists returns different message

IF NOT EXISTS ( SELECT * FROM BL_User_Info WHERE UserName = @UserName )

BEGIN

INSERT INTO BL_User_Info

( UserName ,

[Password] ,

FirstName ,

LastName ,

Email ,

Location ,

Created_By

)

VALUES ( @UserName ,

@Password ,

@FirstName ,

@LastName ,

@Email ,

@Location ,

@Created_By

)

--If User Successfully Registerd then we will return this Message as Output Parameter

--SET @ReturnValue = 0

SET @ReturnValue = @UserName + ' is Registered Successfully'

END

ELSE

BEGIN

--If User already Exists We will return this Message as Output Parameter

--SET @ReturnValue = 1

SET @ReturnValue = @UserName + ' is Already Exists'

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.

Related Posts

About The Author

Pete