Select multiple value and/or ALL for SQL

Terry St Jean 81 Reputation points
2022-11-20T15:59:55.787+00:00

I have a SSRS report where there is a dropdown where the user can select one or multiple product values to filter the report by.
In the prompt, I have to add "ALL" at the top of the list of products, which I can do.
My question is how to handle this 'ALL' value in SQL for the report data.
In the SQL, if they select multiple products, I can have WHERE products in ('the list from the prompt').
But if they select ALL, ALL isn't a product. Or if the select ALL and some products, it should return all products.
I can build the SQL as a text string and if the value ALL is in the prompt string, then exclude this part of the WHERE clause and then EXECUTE sp_executesql but is there a way of handling the 'ALL' value another way other than building the SQL manually?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,021 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,941 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,657 questions
0 comments No comments
{count} votes

Accepted answer
  1. AniyaTang-MSFT 12,446 Reputation points Microsoft Vendor
    2022-11-21T02:02:25.873+00:00

    Hi @Terry St Jean
    Are you trying to achieve this result? Add an "All" option in the parameter drop-down list, when "All" or "All" and other options are selected, all data will be returned.
    262327-1.png
    Then you can try this approach:

    1. Add or ('All' in (@Parameter)) after the Where clause in the main dataset query;
      262362-2.png
    2. Add the "All" option to the query dataset. After the parameters are set normally, the result will be as I have shown.
      262307-3.png
      If I misunderstood you please tell me more information.
      Best regards,
      Aniya
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.