Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Mar 24, 2015
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:

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

SQL Server Return All Records When a Query Parameter is Blank or Null or Empty
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 RSS subscribe by email Subscribe by Email

2 comments :

sujoy santra said...

declare @strsearch varchar(50)=null
select * from @temp where location= isnull(@strsearch,location)

Anonymous said...

@sujoy santra code much better...

Give your Valuable Comments

Other Related Posts

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.