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

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

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

           DROP PROCEDURE dbo.usp_EmployeeDetail

GO

 

CREATE PROCEDURE dbo.usp_EmployeeDetail

@Emp_Code int,

@Emp_Name varchar(50),

@DOJ datetime

AS

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



* * * * *


Email Your Comment To AUTHOR