Pass Output Parameter to Stored Procedure in SQL Server

We need to write stored procedure like as shown below to pass / return output parameter in sql server.

Stored Procedure with Output Parameters

Following is the stored procedure to return output parameters in sql server.

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

If you observe above sql server stored procedure, we are sending "@ERROR" as output parameter. At the end of stored procedure result will be sent back to application "@ERROR" parameter. This is how we need to write queries to return output parameters.