Stored procedure with output parameter in sql server

Introduction:

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 queries to return output parameters