• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

SQL Where Like filter to show all when empty

I have a SQL command to display some recoreds in a GridView.
The view is filtered on the SQL command below:
SelectCommand="SELECT * FROM [ProductIncluded] WHERE ([GFMerchantProductName] LIKE '%' + @GFProductName2 + '%')">
Which works well when I populate @GFProductName2 with a value, however I would like to show all records if the @GFProductName2 is left blank, whereas it returns no records. How would I do this in the same SQL statement rather than a new SQLcommand for when @GFProductName2 is left blank.
Simon Cripps
Simon Cripps
  • 2
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Crippsy,

SelectCommand=" SELECT * FROM [ProductIncluded] WHERE (  ISNULL(@GFProductName2,'') = '' OR    [GFMerchantProductName] LIKE '%' + @GFProductName2 + '% ')">


Simon CrippsAuthor Commented:
Thanks this works fine when running a standalone SQL query. however is not working against the gridview. I suspect that @GFProductName2 is not returning a NULL value despite being empty, I have tried trim, do you know a method to set to null if empty.
simply add    or GFProductName2 is null       to where clause
Simon CrippsAuthor Commented:
Does the job for the command.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now