Programming in MS SQL Server 2012

INPUT Parameter Data

The following example describes CREATE PROCEDURE statement using the minimum required syntax with three different INPUT parameters to return specified employee(s) information based on given parameter data values.

-- Example 104 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

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

DROP PROCEDURE dbo.usp_EmployeeDetail

CREATE PROCEDURE dbo.usp_EmployeeDetail

@Emp_Code int,

@Emp_Name varchar(50),

@DOJ datetime

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 EMP.Emp_Code = @Emp_Code

AND EMP.Emp_Name = @Emp_Name

AND EMP.DOJ = @DOJ

Query Output Screen

The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter data values.

-- Example 105 --

EXECUTE EBusiness.dbo.usp_EmployeeDetail '101', 'Employee 101','12/04/2002'

EXECUTE EBusiness.dbo.usp_EmployeeDetail '102', 'Employee 102','11/12/2002'

EXECUTE EBusiness.dbo.usp_EmployeeDetail '103', 'Employee 103','11/12/2002'

EXECUTE EBusiness.dbo.usp_EmployeeDetail '104', 'Employee 104','10/14/2002'

EXECUTE EBusiness.dbo.usp_EmployeeDetail '105', 'Employee 105','10/14/2002'

Query Output Screen

* * * * *

Your Comment To AUTHOR