Dynamic sql where in sql server

I’ve been recently working a dynamic sql script launched from within a stored procedure that will generate a filter claused based on parameters passed. One the filter clause that i had to take into account was a “WHERE IN”, while passing something like “a,b,c” as parameter, it did not seem to work, so i went a bit in depth and found out that there was some issues while using the WHERE IN clause in dynamic SQL.

The solution found to this problem is to transform the list in the parameter into a table form that can be used in the dynamic WHERE IN clause

[sql]

DECLARE @ParamList NVARCHAR(max)

DECLARE @Query NVARCHAR(4000)

SET @ParamList = ‘hello,this,is,a,list,of,param’

SET @Query = N’SELECT * FROM TableName WHERE ColumnName IN (SELECT param FROM iter_simple_charlist_to_tbl(@ParamList))’

exec sp_executesql @Query, N’@ParamList NVARCHAR(max)’, @ParamList

[/sql]

 

Below is the script for the Table-valued function to convert a given VARCHAR parameter list to table (A small variation is needed for integer type parameter list)

[sql]
/****** Object: UserDefinedFunction [dbo].[iter_simple_charlist_to_tbl] Script Date: 06/17/2011 12:40:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[iter_simple_charlist_to_tbl]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))
DROP FUNCTION [dbo].[iter_simple_charlist_to_tbl]
GO
/****** Object: UserDefinedFunction [dbo].[iter_simple_charlist_to_tbl] Script Date: 06/17/2011 12:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[iter_simple_charlist_to_tbl] (@list nvarchar(MAX))
RETURNS @tbl TABLE (param nvarchar(50) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(‘,’, @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END – @pos – 1
INSERT @tbl (param)
VALUES (substring(@list, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
RETURN
END
[/sql]

References of this implementation are:
http://www.sommarskog.se/arrays-in-sql-2005.html#returnformat
http://www.restfuldevelopment.net/david-kawliche/writing/dynamic-in-without-dynamic-sql/

Note: This solution does not offer an performance optimization and can be inefficient in some scenarios.