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

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


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



* * * * *


Email Your Comment To AUTHOR