Programming in MS SQL Server 2012

RETURN Option

A stored procedure can return an integer value called a return code to indicate the execution status of a stored procedure by using RETURN statement. If a value is not specified, then the stored procedure returns a default value of 0 or 1, depending on the successful execution of the stored procedure.

-- Example 124 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'dbo.usp_DeptEmployeeListReturn' AND type = 'P')

DROP PROCEDURE dbo.usp_DeptEmployeeListReturn

CREATE PROCEDURE dbo.usp_DeptEmployeeListReturn

@Dept_Name varchar(50)

BEGIN

IF EXISTS (SELECT * FROM DEPT WHERE Dept_Name = @Dept_Name)

BEGIN

PRINT 'List of Employees'

SELECT

EMP.Emp_Code,

EMP.Emp_Name,

EMP.DOJ,

EMP.Salary,

EMP.Dept_Code,

DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

WHERE DEPT.Dept_Name = @Dept_Name

RETURN 0

END

ELSE

BEGIN

PRINT 'No Records are found'

RETURN 1

END

Query Output Screen

The following Transact-SQL statements are used to retrieve employee(s) information based on default or given input parameter data value.
RETURN value must store through a variable when the procedure is executed in order to use the return code value in the calling program.

-- Example 125 --

DECLARE @ReturnValue int

EXECUTE @ReturnValue = EBusiness.dbo.usp_DeptEmployeeListReturn 'Marketting'

SELECT @ReturnValue

Query Output Screen

* * * * *

Your Comment To AUTHOR