SQL Server Return All Records If Search Text is Blank

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  

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) +'%'  

Now set SearchText to null or blank.

DECLARE @SEARCHTEXT VARCHAR(50)  
SET @SEARCHTEXT=''  

SELECT * FROM EMPLOYEE WHERE NAME LIKE '%'+ ISNULL(@SEARCHTEXT, NAME) +'%'  

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  

Summary

This article taught us how Return All Records If the Search Text is Blank in SQL Server.