Microsoft® SQL Server 2012 Unleashed

Setting Default Values for Parameters

You can assign a default value to a parameter by specifying a value in the definition of the parameter, as shown in Listing 24.12.

LISTING 24.12 Assigning a Default Value for a Parameter in a Stored Procedure

Click here to view code image

Alter PROCEDURE Emp_Dept @dept_name nvarchar(50) = '%'ASBEGIN   SELECT p.Lastname, p.FirstName, d.Name      FROM HumanResources.Department d      JOIN HumanResources.EmployeeDepartmentHistory edh          ON d.DepartmentID = edh.DepartmentID          AND edh.EndDate IS null      JOIN person.Person p          ON p.BusinessEntityID = edh.BusinessEntityID      WHERE d.name LIKE @dept_name      ORDER BY d.departmentID   RETURN 0END

You can have SQL Server apply the default ...