Programming in MS SQL Server 2012

OUTPUT Parameter Data

The following example describes CREATE PROCEDURE statement using the minimum required syntax with one INPUT and one OUTPUT parameters. By default, OUTPUT parameter returns data value to calling program based on given INPUT parameter data value.

-- Example 112 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

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

DROP PROCEDURE dbo.usp_DeptTotalSalary

CREATE PROCEDURE dbo.usp_DeptTotalSalary

@Dept_Code int,

@@TotalSalary money OUTPUT

SELECT

EMP.Emp_Code,

EMP.Emp_Name,

EMP.Salary,

EMP.Dept_Code,

DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

WHERE EMP.Dept_Code = @Dept_Code

SELECT @@TotalSalary = SUM(Salary) FROM EMP

WHERE EMP.Dept_Code = @Dept_Code

Query Output Screen

The following Transact-SQL statements are used to returns particular department total salary information from EMP table based on given department code data value.

-- Example 113 --

DECLARE @@TotalSalary money

EXECUTE usp_DeptTotalSalary '10', @@TotalSalary OUTPUT

SELECT @@TotalSalary

Query Output Screen

* * * * *

Your Comment To AUTHOR