how to get return value from stored procedure in sql server

Introduction

Here I will discuss how to send/pass output parameter to stored procedure in SQL server or how to utilize stored procedure with with output parameters in SQL server with model or return data from with output parameters utilizing output parameter in SQL server. By utilizing OUT datatype we can send Output parameter to stored procedure in SQL server.

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

CREATE PROCEDURE sp_Customer

@UserName varchar(50),

@Password varchar(50),

@FirstName varchar(50),

@LastName varchar(50),

@Email varchar(50),

@PhoneNo varchar(50),

@Created_By varchar(50),

@ERROR VARCHAR(100) OUT

AS

BEGIN

SET NOCOUNT ON;

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

BEGIN

INSERT INTO customer

(

UserName,

[Password],

FirstName,

LastName,

Email,

PhoneNo,

Created_By

)

VALUES

(

@UserName,

@Password,

@FirstName,

@LastName,

@Email,

@PhoneNo,

@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

At the end of  stored procedure result will be sent back to application "@ERROR" parameter.please set "@ERROR" query to your application