Sometime as per business requirement we need to fetch
all records if search text parameter is blank. We have many different-different
ways to achieve this requirement.
I have below Employee Table in my SQL Server:
SELECT * FROM
EMPLOYEE
Image 1.
CASE 1: Now if we pass a Search Parameter to select
records from this table.
DECLARE @SEARCHTEXT VARCHAR(50)
SET @SEARCHTEXT='Ra'
SELECT * FROM
EMPLOYEE WHERE NAME LIKE
'%'+ ISNULL(@SEARCHTEXT, NAME) +'%'
Image 2.
Now Set SearchText As null or blank.
DECLARE @SEARCHTEXT VARCHAR(50)
SET @SEARCHTEXT=''
SELECT * FROM
EMPLOYEE WHERE NAME LIKE
'%'+ ISNULL(@SEARCHTEXT, NAME) +'%'
Image 3.
Case
2:
DECLARE @SEARCHTEXT VARCHAR(50)
SET @SEARCHTEXT=''
IF(LEN(@SEARCHTEXT)>0)
SELECT * FROM
EMPLOYEE WHERE NAME=@SEARCHTEXT
ELSE
SELECT * FROM
EMPLOYEE WHERE NAME=
NAME
Image 4.