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
- @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 ;
-
-
- 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
- )
-
-
- SET @ReturnValue = @UserName + ' is Registered Successfully'
- END
- ELSE
- BEGIN
-
-
- SET @ReturnValue = @UserName + ' is Already Exists'
- END
- 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.