Tech
News
Videos
Forums
Jobs
Books
Events
More
Interviews
Live
Learn
Training
Career
Members
Blogs
Challenges
Certification
Contribute
Article
Blog
Video
Ebook
Interview Question
Collapse
Feed
Dashboard
Wallet
Learn
Achievements
Network
Refer
Rewards
SharpGPT
Premium
Contribute
Article
Blog
Video
Ebook
Interview Question
Register
Login
How To Pass Output Parameter To Stored Procedure In SQL Server
WhatsApp
Nikunj Satasiya
7y
23.1
k
0
5
25
Blog
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]
--CREATED ON 08/12/2017 BY NIKUNJ SATASIYA
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
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.
Stored Procedure
MS SQL
SQL Server
SQL
Database
Up Next
Optional parameters in SQL Server Stored Procedures
Ebook Download
View all
Functions in SQL Server: Practical Guide
Read by 9k people
Download Now!
Learn
View all
Codingvila
Codingvila is an educational website, developed to help tech specialists/beginners.
Membership not found