Programming in MS SQL Server 2012

Default Parameter Data



The following example describes CREATE PROCEDURE with default parameter value. It is optional to pass parameter data value while execute this stored procedure. By default, this stored procedure return SYSTEM department employee(s) information.

The default data value must be a CONSTANT or NULL. It can include wildcard characters (% or _ or [ ] and [^]) if the stored procedure uses the parameter with the LIKE keyword.

-- Example 106 --

 

USE EBusiness

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

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

           DROP PROCEDURE dbo.usp_DeptEmployeeList

GO

 

CREATE PROCEDURE dbo.usp_DeptEmployeeList

@Dept_Name varchar(50) = 'System'

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 DEPT.Dept_Name = @Dept_Name


Query Output Screen



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

-- Example 107 --

 

EXECUTE EBusiness.dbo.usp_DeptEmployeeList

 

EXECUTE EBusiness.dbo.usp_DeptEmployeeList 'Accounts'

 

EXECUTE EBusiness.dbo.usp_DeptEmployeeList 'Marketting'

 

EXECUTE EBusiness.dbo.usp_DeptEmployeeList 'Administration'


Query Output Screen



* * * * *


Email Your Comment To AUTHOR