Programming in MS SQL Server 2012 |
OUTPUT Parameter DataThe 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 GO IF EXISTS (SELECT *
FROM
sysobjects
WHERE
name = N'dbo.usp_DeptTotalSalary' AND
type = 'P')
DROP
PROCEDURE dbo.usp_DeptTotalSalary GO CREATE PROCEDURE dbo.usp_DeptTotalSalary @Dept_Code int, @@TotalSalary money OUTPUT AS 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 --
Example 113 -- DECLARE @@TotalSalary
money EXECUTE usp_DeptTotalSalary '10', @@TotalSalary OUTPUT SELECT @@TotalSalary |
* * * * *