t-sqlsearchstored-procedureswhere-clausein-subquery

SQL where clause with optional IDs

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

Solution

  • 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