0
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

James Z
  • 12,209
  • 10
  • 24
  • 44
Thamar
  • 3
  • 2

1 Answers1

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
De Wet van As
  • 904
  • 8
  • 27
  • tblEmployee.empId is the primary key and is not blank. If @empIds is blank I want all records from tblEmployee. – Thamar May 16 '23 at 04:49
  • I misread that. See my updated version. You can then just do the blank check on @empIds rather than empId. – De Wet van As May 16 '23 at 07:04