SQL where clause with optional IDs

Question

Create Proc prcEmployeeSearch(
@empIds varchar(200)=''
)
As
Select empId, empName from tblEmployee
Where empId in (select item from dbo.Split(@empIds,',')
Go

Exec prcEmployeeSearch ''
Go
Exec prcEmployeeSearch '1,2,5'
Go

when @empIds is empty I want retrieve all rows and if not empty only passed empIds by using above procedure.

dbo.Split is a custom comma separated split tabular function.

Kindly give some solution without affecting query performance

Answer 1

You can achieve this by simply adding an OR to your where clause:

Create Proc prcEmployeeSearch(
@empIds varchar(200)=''
)
As
Select empId, empName from tblEmployee
Where 
(empId in (select item from dbo.Split(@empIds,','))
OR
(@empIds = '')
Go

Exec prcEmployeeSearch ''
Go
Exec prcEmployeeSearch '1,2,5'
Go