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

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

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

           DROP PROCEDURE dbo.usp_DeptEmployeeListReturn

GO

 

CREATE PROCEDURE dbo.usp_DeptEmployeeListReturn

@Dept_Name varchar(50)

AS

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

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



* * * * *


Email Your Comment To AUTHOR