user3697498 user3697498 - 3 days ago 7
SQL Question

Where Clause with IN and Coalesce

Say I have a table, Music:

Genre Artist Song
Rock Rolling Stones Brown Sugar
Rock Bob Seger Night Moves
Country Eric Church Record Year
Pop Bruno Mars Grenade


And I have

DECLARE @Genre VarChar(MAX) = NULL
DECLARE @Artist VarChar(MAX) = NULL

SELECT *
FROM Music M
WHERE (M.Genre = COALESCE(@Genre, M.Genre) OR
M.Artist = COALESCE(@Artist, M.Artist))


This gives me the option to chose a genre and add a random artist that I would also like to see.

But what if I want to pass in multiple Artists as a string,
i.e.

@Genre = Rock
@Artist = 'Eric Church, Bruno Mars'


where in essence I would need an IN statement but also have to handle instances where the variable is NULL?

I am using SQL SERVER.

Thank you

Answer
  1. you can use CHARINDEX, but it performance is low.

    SELECT *
    FROM Music M
    WHERE CHARINDEX(','+M.Genre+',',','+@Genre+',' ) >0 OR CHARINDEX(','+M.Artist+',',','+@Artist+',' ) >0
    
  2. Dynamic statement´╝Ü

    DECLARE @Genre VarChar(MAX) = NULL
    DECLARE @Artist VarChar(MAX) = NULL
    DECLARE @sql NVARCHAR(max)
    SET @sql='
          SELECT * 
          FROM Music M
          WHERE M.Genre in ('+@Genre+') OR M.Artist in ('+@Artist+')'
    EXEC(@sql)
    
  3. using split string function

  4. Handling the NULL,you can change statement to.

     SET @sql='
          SELECT * 
          FROM Music M
          WHERE M.Genre in ('+ISNULL(@Genre,'M.Genre')+') OR M.Artist in ('+ISNULL(@Artist,'M.Artist')+')'
    

When @Genre and @Artist is null, the script will be below:

SELECT * FROM Music M
WHERE M.Genre in (M.Genre) OR M.Artist in (M.Artist)'

1 in (1) always be true

Comments