SSRS: SQL 'IF' logic based on multiple value parameter

Question

My SSRS report contains a multiple value variable called Brand.

The user can select one or more different brands.

This is passed to a Microsoft SQL query, and based on this the query should do perform various steps.

Example:

IF BrandA in @Brand THEN select * from things in database A;

IF BrandB in @Brand THEN select * from things in database B;

IF BrandB in @Brand THEN select * from things in database C;

What is the correct syntax for this?

Also, how can I test it in SQL before putting into my report? As I don't think you can create multiple value variables in SQL easily. (Or am I wrong?)

Alternatively, I can have the user type in the selection as BrandA,BrandB,BrandC, and then say:

If @Brand like '%BrandA%' then...

Obviously the first option is cleaner, if it will actually work. Please help.

Answer 1

You could use:

SELECT  Brand = 'BrandA', Column1, Column2, Column3
FROM    Database1.dbo.Table
WHERE   'BrandA' IN (@Brand)
UNION ALL
SELECT  Brand = 'BrandB', Column1, Column2, Column3
FROM    Database2.dbo.Table
WHERE   'BrandB' IN (@Brand)
UNION ALL
SELECT  Brand = 'BrandC', Column1, Column2, Column3
FROM    Database3.dbo.Table
WHERE   'BrandC' IN (@Brand);

---

ADDENDUM

With regard to the comments, the reason I have put @Brand in parentheses is because this is how to use multivalued parameters with the IN syntax.

With regard to it being inefficient querying the database when you know you don't need the results, you won't actually query the tables you don't need, SQL Server is smart enough to work out the constant expression first and not bother reading the table when it isn't necessary. Imagine 3 tables (T1, T2, T3) all identical as follows:

CREATE TABLE T1 (ID INT IDENTITY, Filler CHAR(1000));
INSERT T1 (Filler)
SELECT NULL FROM sys.all_objects;

Now that we have 3 identical tables filled with example data I then ran this query:

DECLARE @Brand TABLE (Brand INT);
INSERT @Brand (Brand) VALUES (1), (3);

SELECT  Brand = 1, ID, Filler
FROM    T1
WHERE   1 IN (SELECT Brand FROM @Brand)
UNION ALL
SELECT  Brand = 2, ID, Filler
FROM    T2
WHERE   2 IN (SELECT Brand FROM @Brand)
UNION ALL
SELECT  Brand = 3, ID, Filler
FROM    T3
WHERE   3 IN (SELECT Brand FROM @Brand);

SELECT *
FROM T1
WHERE 1 = 0

The IO statistics show:

(5230 row(s) affected)

Table '#1C299A82'. Scan count 3, logical reads 5231, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'T3'. Scan count 1, logical reads 374, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'T2'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'T1'. Scan count 1, logical reads 374, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The key being T2 is not actually read, the logical read showing against it is reading from @brand because I cannot replicate the nature of multivalued parameters in SSMS, if I were to actually run:

SELECT  Brand = 2, ID, Filler
FROM    T2
WHERE   2 IN (1, 3);

There would no no reads at all.

---

EDIT2

This should still work with OPENQUERY, to demonstrate I have removed the UNION (as the actual IO reads are masked slightly by OPENQUERY), but used the same 3 test tables as above:

SET STATISTICS IO ON;
DECLARE @Brand TABLE (Brand INT);
INSERT @Brand (Brand) VALUES (1), (3);

SELECT  Brand = 1, ID, Filler
FROM    OPENQUERY([ServerName], 'SELECT ID, Filler FROM TestDB.dbo.T1')
WHERE   1 IN (SELECT Brand FROM @Brand)

SELECT  Brand = 2, ID, Filler
FROM    OPENQUERY([ServerName], 'SELECT ID, Filler FROM TestDB.dbo.T2')
WHERE   2 IN (SELECT Brand FROM @Brand)

SELECT  Brand = 3, ID, Filler
FROM    OPENQUERY([ServerName], 'SELECT ID, Filler FROM TestDB.dbo.T3')
WHERE   3 IN (SELECT Brand FROM @Brand);

(2615 row(s) affected)

Table '#3A17D891'. Scan count 1, logical reads 2615, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)

Table '#3A17D891'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2615 row(s) affected)

Table '#3A17D891'. Scan count 1, logical reads 2615, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Agin this shows no reads on T2 because the outer where condition evaluates to false, the single read again is on the brand table, running the following with constants yields no reads:

SELECT  Brand = 2, ID, Filler
FROM    OPENQUERY([ServerName], 'SELECT ID, Filler FROM TestDB.dbo.T2')
WHERE   2 IN (1, 3);

The reason I am suggesting UNION ALL to combine all your queries rather than using IF is that you either need to create a condition for all possible combination of brands, use temp tables, or use dynamic SQL to combine your three results into a single result set. e.g.

IF 'BrandA' IN (@Brand)
    BEGIN
        SELECT  <Columns>
        FROM    Database1.dbo.Table
    END
IF 'BrandB' IN (@Brand)
    BEGIN
        SELECT  <Columns>
        FROM    Database1.dbo.Table
    END
IF 'BrandC' IN (@Brand)
    BEGIN
        SELECT  <Columns>
        FROM    Database2.dbo.Table
    END

Is going to yield one set of results per brand, and your SSRS data set will only use one of them, to avoid dynamic SQL or temp tables you would need something like:

IF 'BrandA' IN (@Brand) AND 'BrandB' IN (@Brand) AND 'BrandC' IN (@Brand)
    BEGIN
        --UNION ALL THREE TOGETHER
    END
ELSE IF 'BrandA' IN (@Brand) AND 'BrandB' IN (@Brand)
    BEGIN
        --UNION ALL BRANDA AND BRANDB TOGETHER
    END
ELSE IF 'BrandA' IN (@Brand) AND 'BrandC' IN (@Brand)
    BEGIN
        --UNION ALL BRANDA AND BRANDC TOGETHER
    END
ELSE IF 'BrandA' IN (@Brand)
    BEGIN
        --JUST BRANDA
    END
ELSE IF 'BrandB' IN (@Brand) AND 'BrandC' IN (@Brand)
    BEGIN
        --UNION ALL BRANDB AND BRANDC TOGETHER
    END
ELSE IF 'BrandB' IN (@Brand)
    BEGIN
        --JUST BRANDB
    END
ELSE IF 'BrandC' IN (@Brand)
    BEGIN
        --JUST BRANDC
    END

Which I think you'll agree is bad enough for only 3 possible values, it will get ridiculous if you have more. Temp tables might be a viable approach:

SET NOCOUNT ON;
CREATE TABLE #T (<columns>);

IF 'BrandA' IN (@Brand)
    BEGIN
        INSERT #T (<columns>)
        SELECT  <Columns>
        FROM    Database1.dbo.Table
    END
IF 'BrandB' IN (@Brand)
    BEGIN
        INSERT #T (<columns>)
        SELECT  <Columns>
        FROM    Database1.dbo.Table
    END
IF 'BrandC' IN (@Brand)
    BEGIN
        INSERT #T (<columns>)
        SELECT  <Columns>
        FROM    Database2.dbo.Table
    END

SELECT  *
FROM    #T;

But as demonstrated, you don't actually perform the query with the constant expression in the where clause, so you are not actually gaining anything from doing this other than the overhead of creating and inserting into the temporary table.

Answer 2

You can try something like this.

DECLARE @Brand

IF BrandA IN (@Brand)

BEGIN
   USE DATABASE_A
   SELECT * FROM TABLE
END

ELSE IF BrandB IN (@Brand)

BEGIN
   USE DATABASE_B
   SELECT * FROM TABLE
END

ELSE 

BEGIN
   USE DEFAUL_DATABASE
   SELECT * FROM Table
END