SQL Where Like filter to show all when empty

Posted on 2010-01-04
Last Modified: 2012-05-08
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.
Question by:Simon Cripps
    LVL 75

    Accepted Solution

    Hello Crippsy,

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



    Author Comment

    by:Simon Cripps
    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.
    LVL 7

    Expert Comment

    simply add    or GFProductName2 is null       to where clause

    Author Closing Comment

    by:Simon Cripps
    Does the job for the command.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now