Pass Output Parameter to Stored Procedure in SQL Server

 Introduction:

Here I will explain how to send / pass output parameter to stored procedure in sql server or how to use stored procedure with output parameters in sql server with example or return data from stored procedure using output parameter in sql server. By using OUT datatype we can send output parameter to stored procedure in sql server.

Description:

In previous articles I explained sql server group by alias column name, sql server single stored procedure to insert / update / delete / select, sql server get next / previous values from sequence, sql server keyboard shortcut keys in management studio, sql server show exact match values first then partial match values and many articles related to sql server.  Now I will explain how to pass or return out parameter in 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.