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:

SQL
SELECT * FROM EMPLOYEE

employee.png

CASE 1

Now if we pass a Search Parameter to select records from this table.

SQL
DECLARE @SEARCHTEXT VARCHAR(50)  
SET @SEARCHTEXT='Ra'
SQL
SELECT * FROM EMPLOYEE WHERE NAME LIKE '%'+ ISNULL(@SEARCHTEXT, NAME) +'%'

case1.png

Now set SearchText to null or blank.

SQL
DECLARE @SEARCHTEXT VARCHAR(50)  
SET @SEARCHTEXT=''
SQL
SELECT * FROM EMPLOYEE WHERE NAME LIKE '%'+ ISNULL(@SEARCHTEXT, NAME) +'%'

name like.png

Case 2

SQL
DECLARE @SEARCHTEXT VARCHAR(50)  
SET @SEARCHTEXT=''
SQL
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.