Select all records when multiple value parameter is blank

Question

I'm creating a report in SSRS and want to have a multiple value parameter but allow blank values (''), and display all records when blank.

The gist of it is:

SELECT *
FROM Products p
JOIN ProductCategories c on c.ProductId = p.Id
WHERE (c.Name IN (@Categories) OR @Categories = '')

Which works when blank, and works with 1 category, but errors out with 2 categories. We got around this by using a temp table, but that solution seemed sort of hacky, so I wanted to see if there was a better way to resolve this.

The temp table workaround we built was this:

CREATE TABLE #temp (ProductId INT, Category NVARCHAR(MAX))

INSERT INTO #temp
SELECT p.Id, c.Name
FROM Products p
JOIN ProductCategories c on c.ProductId = p.Id
WHERE c.Name IN (@Categories)

IF ((SELECT COUNT(*) FROM #temp) = 0)
BEGIN
INSERT INTO #temp
SELECT p.Id, c.Name
FROM Products p
JOIN ProductCategories c on c.ProductId = p.Id
WHERE c.Name LIKE '%'
END

SELECT * FROM #temp

Thanks in advance!

SQL Server is declarative by design and does not support macro substitution. That said, what version of SS and how are you passing @Categories ? — John Cappelletti, Jan 10 '19 at 21:56
[Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) is one of many other questions on this topic. — HABO, Jan 10 '19 at 22:17

John Cappelletti · Answer 1 · 2019-01-10T22:14:56.947

If you don't have a split/parse function

Example

 ...
 Where @Categories = ''
       or
       C.Name in (
                    Select RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                    From  (Select x = Cast('<x>' + replace((Select replace(@Categories,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                    Cross Apply x.nodes('x') AS B(i)
                 )

Here is a dbFiddle ... You'll notice Poultry was excluded, then try it when @Categories=''

Select all records when multiple value parameter is blank

1 Answers1