Pass Output Parameter using Stored Procedure in SQL Server

Introduction

In this article i will explain about How to pass a output parameter in Stored Procedure and return that out parameter value in your application.

Practice

Here i am writing my Stored Procedure to return the output or something as Text using out parameter.

CREATE PROCEDURE usp_EmployeeInfo

@EmpName 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 Employee exists or not , Return different message in both cases if exist or not.

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

BEGIN

INSERT INTO EmployeeInfo

(

EmpName,

[Password],

FirstName,

LastName,

Email,

PhoneNo,

Location,

Created_By

)

VALUES

(

@EmpName,

@Password,

@FirstName,

@LastName,

@Email,

@PhoneNo,

@Location,

@Created_By

)

--If Employee Successfully Registerd returing this Message as Output Parameter

SET @ERROR=@EmpName+' Registered Successfully'

END

ELSE

BEGIN

--If Employee already Exists  returning this Message as Output Parameter

SET @ERROR=@EmpName+ ' Already Exists'

END

END

Here you can see i  used @ERROR as out parameter and based on this value we can display this message in our application while we call this Procedure.