Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

SQL Server- Return All Record If SearchText is Blank

Article:
Viewed:  896 
Posted On:  20/04/2015 19:15:24 
How we can select All records from a SQL Server table if Search Text is null or blank ? 

 

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

1.png

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

2.png

Image 2.

Now Set SearchText As null or blank.

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

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

3.png

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

 

 4.png

 

Image 4.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us