Parameterized IN Queries

{

I haven't listened to the podcast yet but saw a cool trick from Joel Spolsky on approaching parameterized IN queries. Purists will bemoan its lack of premature optimization but I think it's novel enough to study because of the approach: using the SQL LIKE operator on your parameter rather than a field, which is what people like me are used to. There's code on the StackOverflow post but I thought I'd paste some of the poking around I did in Sql Management Studio:

-- setup
CREATE TABLE Person
(
      PersonID INT IDENTITY(1,1) PRIMARY KEY, 
      FirstName VARCHAR(50) NULL
)
GO

-- some data 
INSERT INTO Person VALUES('David')
INSERT INTO Person VALUES('Jonathan')
INSERT INTO Person VALUES('Zlatan')
INSERT INTO Person VALUES('Trilby')

-- here's the magic
DECLARE @FirstName VARCHAR(50)
SET @FirstName = '|David|Trilby|'
SELECT * FROM Person WHERE @FirstName like '%|' + FirstName + '|%'

-- ported to a proc
CREATE PROC uspPersonSelector
      @FirstNames VARCHAR(500)
AS
      SELECT * FROM Person WHERE @FirstNames like '%|' + FirstName + '|%'
GO

-- showing it works
uspPersonSelector '|David|Trilby|'

-- somewhere in the netherworld of C#: 
/* 
string[] names = {"David", "Trilby"};
SqlCommand cmd = GetACommandFromSomewhere();
cmd.Parameters.AddWithValue("@FirstNames", "|".Join(names));
*/

--teardown
Drop Table Person 

 

}