Sunday, April 20, 2008

Quick look at IN predicate

There are lots of articles and techniques onnthe internet about how to deal with delimited parameters. Last Wed I was visited a client that asked to write a quick query to return the data based on delimited values . Here we go

Use pubs

DECLARE @t VARCHAR(50)
SET @t = ('Bennet,smith')

SELECT *
FROM authors
WHERE ',' + @t + ',' LIKE '%,' + au_lname + ',%'

SELECT * FROM
authors WHERE CHARINDEX(',' + au_lname + ',',','+ @t+',')>0

As you can imagine, performance will be horrible.