Introduction
Sometimes, depending on business requirements, we need to fetch all records if the search text parameter is blank. We have many ways to satisfy that requirement.
I have the following Employee Table in my SQL Server:
SELECT * FROM EMPLOYEE
![employee.png]()
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) +'%'
![case1.png]()
Now set SearchText to null or blank.
DECLARE @SEARCHTEXT VARCHAR(50)
SET @SEARCHTEXT=''
SELECT * FROM EMPLOYEE WHERE NAME LIKE '%'+ ISNULL(@SEARCHTEXT, NAME) +'%'
![name like.png]()
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
![case 2.png]()
Summary
This article taught us how Return All Records If the Search Text is Blank in SQL Server.