Conditional Join or Filter based on Variable

Question

I have a stored procedure that I'm passing 22 string variables to from SSRS. The variables can either be null or be a string. When a Null value is passed to the stored procedure, I need to return all records. While if the a string is passed, I need to filter to only those values. In my example, I'm using string_split and saving the variables into a temporary table.

Drop table if exists #Authors
Drop table if exists #Temp
 
CREATE TABLE #Authors
(
Id INT PRIMARY KEY,
Names VARCHAR (50) NOT NULL,
)


INSERT INTO #Authors
VALUES (1, 'AuthorA'),
(2, 'AuthorB'),
(3, 'AuthorC'),
(10, 'AuthorD'),
(12, 'AuthorE')

DECLARE
@vartest1 AS VARCHAR(20)
SET @vartest1 = 'AuthorA,AuthorB'

SELECT VALUE AS Names INTO #Temp FROM string_split(@vartest1, ',')
SELECT * FROM #Temp

SELECT * FROM #Authors a
INNER JOIN #TEMP t
ON a.Names=t.Names

SELECT *
FROM #Authors a
INNER JOIN 
    CASE    
        WHEN len(@vartest1)>0 #Temp t
        ELSE #Authors a
    END
ON 
    CASE
        WHEN len(@vartest1)>0 Then #Temp.Names
        Else a.Names
    END = a.Names

Then I try to create a case join where it either joins to the temporary table or on itself to return all. I've read where people have used unions, but I don't think that'd work for 22 parameters.

case is an expression, not a statement, and as such can't return anything more than a scalar value. You need to left join both tables with the case in the on condition. — Dale K
– Dale K, Commented May 11, 2022 at 21:31
_ passing 22 string variables_ That seems excessive but sometimes we have no other option. Suggest you read Erland's dynamic search conditions and Aaron's kitchen sink example for in-depth discussion — SMor
– SMor, Commented May 11, 2022 at 23:12

EdmCoff · Accepted Answer · 2022-05-11 21:50:51Z

1

Have you considered doing a left join with a condition that checks whether the variable was null?

Something like

SELECT a.* FROM #Authors a
 LEFT JOIN #TEMP t
  ON a.Names=t.Names
WHERE
 @vartest1 is null -- Include all if input is null...
 or t.names is not null -- ... Otherwise exclude where no match in #TEMP (treat like inner join)

I think this meets your requirements. The left join ensures we have all the records in #Authors, then the WHERE handles the two possible cases (@vartest1 is null to get them all or t.names is not null to check if there was actually a match).

You are likely aware of this, but there's no need to use a temporary table to handle your split values. You can do:

SELECT a.* FROM #Authors a
 LEFT JOIN string_split(@vartest1, ',') t
  ON a.Names=t.value
WHERE @vartest1 is null or t.value is not null

I think the above is very reasonable, but something more along your original thoughts would be to use IF like:

IF @vartest1 is null
 SELECT a.* FROM #Authors a;
ELSE
 SELECT a.* FROM #Authors a inner join #Temp t ON a.Names=t.Names;

edited May 11, 2022 at 21:50

answered May 11, 2022 at 21:44

EdmCoff

3,6061 gold badge12 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dcrowley01 Over a year ago

This got me there.

Collectives™ on Stack Overflow

Conditional Join or Filter based on Variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related