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.
Requirement
1) Create Stored Procedure For Insert and Update Record Within Table in SQL Server.
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
![]() |
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.