It creates a most efficient dynamic stored procedure
Create Procedure dbo.SearchRP_Demand
@HCID int = 0,
@OPPID varchar(20) = '',
@StartDate datetime = '',
@EndDate datetime = '',
@HeadCount int = 0,
@JobID int = 0,
@DomainId int = 0,
@DomainName varchar(100) = '',
@EmpJAILevel varchar(50) = '',
@EmpJAILevelId int = 0,
@Location varchar(50) = '',
@LocationID int = 0,
@EmployeeType varchar(100) = '',
@EmployeeTypeId int = 0,
@SGName nvarchar(50) = '',
@SGID varchar(50) = ''
AS
BEGIN
Select
[HCID],
[OPPID],
[StartDate],
[EndDate],
[HeadCount],
[JobID],
[DomainId],
[DomainName],
[EmpJAILevel],
[EmpJAILevelId],
[Location],
[LocationID],
[EmployeeType],
[EmployeeTypeId],
[SGName],
[SGID]
FROM dbo.RP_Demand
WHERE
1 = Case When @HCID = 0 Then 1 When @HCID = HCID Then 1 END AND
1 = Case When @OPPID = '' Then 1 When OPPID like '%' + @OPPID + '%' Then 1 END AND
1 = Case When @StartDate = '' Then 1 When @StartDate = StartDate Then 1 END AND
1 = Case When @EndDate = '' Then 1 When @EndDate = EndDate Then 1 END AND
1 = Case When @HeadCount = 0 Then 1 When @HeadCount = HeadCount Then 1 END AND
1 = Case When @JobID = 0 Then 1 When @JobID = JobID Then 1 END AND
1 = Case When @DomainId = 0 Then 1 When @DomainId = DomainId Then 1 END AND
1 = Case When @DomainName = '' Then 1 When DomainName like '%' + @DomainName + '%' Then 1 END AND
1 = Case When @EmpJAILevel = '' Then 1 When EmpJAILevel like '%' + @EmpJAILevel + '%' Then 1 END AND
1 = Case When @EmpJAILevelId = 0 Then 1 When @EmpJAILevelId = EmpJAILevelId Then 1 END AND
1 = Case When @Location = '' Then 1 When Location like '%' + @Location + '%' Then 1 END AND
1 = Case When @LocationID = 0 Then 1 When @LocationID = LocationID Then 1 END AND
1 = Case When @EmployeeType = '' Then 1 When EmployeeType like '%' + @EmployeeType + '%' Then 1 END AND
1 = Case When @EmployeeTypeId = 0 Then 1 When @EmployeeTypeId = EmployeeTypeId Then 1 END AND
1 = Case When @SGName = '' Then 1 When SGName like '%' + @SGName + '%' Then 1 END AND
1 = Case When @SGID = '' Then 1 When SGID like '%' + @SGID + '%' Then 1 END
END