Conditional Join or Filter based on Variable-CodePudding

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.

CodePudding user response：

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;