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

IF EXISTS (SELECT *

FROM sysobjects

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

DROP PROCEDURE dbo.usp_DeptEmployeeList

CREATE PROCEDURE dbo.usp_DeptEmployeeList

@Dept_Name varchar(50) = 'System'

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

* * * * *

Your Comment To AUTHOR