SQL Server Return All Records When a Query Parameter is Blank or Null or Empty

Introduction:

Here I will explain how to return all the records when a query parameter is blank or empty or null in sql server or query get all the records from database when input parameter is null or blank or empty in sql server. We need to write a condition to check whether query parameter is blank or not in sql server based on that we can return all records or matched records.

Description:

In previous articles I explained joins in sql server, function example in sql server 2008, Primary key constraint in sql server, foreign key constraint in sql server, query to remove first and last characters from string in sql server, Pass XML file as parameter from C# to SQL stored procedure, cursor example in sql server and many articles relating to SQL server. Now I will explain how to return all the records when a query parameter is blank or empty or null in sql server.

To return all records incase if input parameter is empty or null for that we will check with one simple example. First write the query like as shown below

declare @temp table(id int, name varchar(50), location varchar(50)) insert into @temp(id,name,location) values(1,'suresh','chennai') insert into @temp(id,name,location) values(2,'rohini','chennai') insert into @temp(id,name,location) values(3,'praveen','guntur') insert into @temp(id,name,location) values(4,'sudheer','vizag') insert into @temp(id,name,location) values(5,'sateesh','vizag') insert into @temp(id,name,location) values(6,'madhav','nagpur') declare @strsearch varchar(50) set @strsearch='' -- if you want check with null just comment this line if(LEN(@strsearch)>0) select * from @temp where location= @strsearch else select * from @temp where location= location

If you observe above query I am passing @strsearch variable as blank. Now execute this query and check results it will return data like as shown below

Output

Now we will check by passing value to @strsearch variable for that change query like as shown below

declare @temp table(id int, name varchar(50), location varchar(50)) insert into @temp(id,name,location) values(1,'suresh','chennai') insert into @temp(id,name,location) values(2,'rohini','chennai') insert into @temp(id,name,location) values(3,'praveen','guntur') insert into @temp(id,name,location) values(4,'sudheer','vizag') insert into @temp(id,name,location) values(5,'sateesh','vizag') insert into @temp(id,name,location) values(6,'madhav','nagpur') declare @strsearch varchar(50) set @strsearch='chennai' if(LEN(@strsearch)>0) select * from @temp where location= @strsearch else select * from @temp where location= location

If you observe above query I am passing @strsearch variable as 'chennai' based on that will return the result like as shown below

Output

I hope it helps you….

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email