Parameterizing an IN Expression

The last section discussed using parameters to secure your code with ad hoc queries. That works fine when you are doing a comparison in a where clause. However, it doesn't work at all if you want to pass a parameter to an IN expression. For example, the following does not work: select EmployeeID, LastName from Employees
where EmployeeID in (@list)
One way to get around this limitation is to use a SQL function. An IN will accept a table. Therefore, you can call a function that parses each element in a comma-separated list, passed in the parameter, and build a table. The function's return value would be the table, which works fine with the IN. Listing 14-4 shows a function that accomplishes this.
Listing 14-4: A function that accepts a parameter and returns a table

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION dbo.ufStringToIntTable (@list varchar(8000))
RETURNS @tbl TABLE (val int,seqnum int) AS
BEGIN
DECLARE @ix int,
@pos int,
@seq int,
@str varchar(8000),
@num int
SET @pos = 1
SET @ix = 1
SET @seq = 1
WHILE @ix > 0
BEGIN
-- extract next parameter
SET @ix = charindex(',', @list, @pos)
IF @ix > 0
SET @str = substring(@list, @pos, @ix - @pos)
ELSE
SET @str = substring(@list, @pos, len(@list))
SET @str = ltrim(rtrim(@str))
-- ensure valid number
IF @str LIKE '%[0-9]%' AND
(@str NOT LIKE '%[^0-9]%' OR
@str LIKE '[-+]%' AND
substring(@str, 2, len(@str)) NOT LIKE '[-+]%[^0-9]%')
BEGIN
-- convert and add number to table
SET @num = convert(int, @str)
INSERT @tbl (val,seqnum) VALUES(@num, @seq)
END
-- prepare for next parameter
SET @pos = @ix + 1
SET @seq = @Seq + 1
END
-- return table with all parameters
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The function in Listing 14-4 accepts a parameter and returns a table. The content of the input parameter is a comma-separated list of numbers. The function parses the values, converts each value to an integer, and adds each integer as a row into the table. This dynamically created table is then returned to the caller. Because an IN expression accepts a table, this works out well. Listing 14-5 shows how this function can be used to accept a parameter for an IN expression.
Listing 14-5: Using a function for an IN Expression

View Employees

">SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Photo]
FROM [Employees] WHERE ([EmployeeID] IN (select val from
ufStringToIntTable(@EmployeeID)))">

Listing 14-5 uses a SqlDataSource control to populate a GridView control. Notice the IN expression in the SelectCommand attribute. It contains …WHERE ([EmployeeID] IN (select val from ufString ToIntTable(@EmployeeID))). ufStringToIntTable is the function from Listing 14-4. As shown in the SelectParameters element, the @EmployeeID parameter is populated from the txtEmployeeIDs TextBox control. You can test this by running the Web form in Listing 14-5 and adding a comma-separated string of numbers. The Northwind Employees table contains nine records, so you can select some, none, or all of them. Pressing the Update button causes a postback that repopulates the GridView control.