How to write SQL Query to return output parameters

Here I will explain how to write query to return output parameters in SQL Server.

Description:

In some situations we need to return the output value after execution of our query here I will explain with simple user registration here I am returning one out parameter if the query executes successfully else I am returning another output parameter .

Query:

CREATE PROCEDURE sp_userinformation
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(50),
@PhoneNo varchar(50),
@Location varchar(50),
@Created_By varchar(50),
@ERROR VARCHAR(100) OUT

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message

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

BEGIN

INSERT INTO User_Information
(
UserName,
[Password],
FirstName,
LastName,
Email,
PhoneNo,
Location,
Created_By
)
VALUES
(
@UserName,
@Password,
@FirstName,
@LastName,
@Email,
@PhoneNo,
@Location,
@Created_By
)

--If User Successfully Registerd I am returing this Message as Output Parameter
SET @ERROR=@UserName+' Registered Successfully'

END

ELSE

BEGIN

--If User already Exists i am returning this Message as Output Parameter

SET @ERROR=@UserName + ' Already Exists'

END

END






This way we will write queires to return output parameters