Sp_executesql with the 'IN' statement

Question

I am trying to use sp_executesql to prevent SQL injection in SQL 2005, I have a simple query:

SELECT * from table WHERE RegionCode in ('X101', 'B202')

However, when I use sp_executesql to do the following, it returns nothing.

Set @Cmd = N'SELECT * FROM table WHERE RegionCode in (@P1)'
SET @ParamDefinition = N'@P1 varchar(100)';
DECLARE @Code as nvarchar(100);
SET @Code = 'X101,B202'
EXECUTE sp_executesql @Cmd, @ParamDefinition, @P1 = @Code

This is what I tested:

SET @Code = 'X101'   <-- This works, it returns a single region
SET @Code = 'X101,B202'   <--- Returns nothing
SET @Code = '''X101'',''B202'''  <-- Returns nothing

Please help .... what have I done wrong?

Answer 1

The reason it doesn't work is because @ P1 is treated as one, one value.

. @Code - X101, B202, : SELECT * FROM WHERE RegionCode IN ('X101, B202') , RegionCode , @P1. , , , RegionCode, , .

@Code @Cmd sql, , :

SET @Code = '''X101'',''B202'''
SET @Cmd = 'SELECT * FROM Table WHERE RegionCode IN (' + @Code + ')'
EXECUTE (@Cmd)

, SQL-, , , , .

, .

, SQL Server 2005. CSV "Value1, Value2, Value3", TABLE, ( , Google ). TABLE var . XML, , XML SQL Server. , SQL.

SQL Server 2008, : 3- , , .

Answer 2

SQL Server. PRO CON :

" SQL Server 2005 Beyond, " Erland Sommarskog

split. :

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

TSQL, SQL Server, . , PRO CON .

Numbers Table , Numbers, 1 10000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Numbers , :

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

CSV , , sql. :

DECLARE @Cmd as nvarchar(1000),@ParamDefinition nvarchar(1000);
Set @Cmd = N'SELECT * FROM table WHERE RegionCode in (SELECT ListValue FROM dbo.FN_ListToTable('','',@P1))'
SET @ParamDefinition = N'@P1 varchar(100)';
DECLARE @Code as nvarchar(1000);
SET @Code = 'X101,B202'
EXECUTE sp_executesql @Cmd, @ParamDefinition, @P1 = @Code

- , ( ):

CREATE TABLE YourTable (PK int primary key, RowValue varchar(5))
INSERT YourTable VALUES (1,'A')
INSERT YourTable VALUES (2,'BB')
INSERT YourTable VALUES (3,'CCC')
INSERT YourTable VALUES (4,'DDDD')
INSERT YourTable VALUES (5,'EEE')
INSERT YourTable VALUES (6,'FF')
INSERT YourTable VALUES (7,'G')

DECLARE @SQL              nvarchar(1000)
       ,@ParamDefinition  nvarchar(1000)
       ,@ParamValue       varchar(100)
SELECT @SQL = N'SELECT * FROM YourTable WHERE PK IN (SELECT ListValue FROM dbo.FN_ListToTable('','',@P1))'
      ,@ParamDefinition = N'@P1 varchar(100)'
      ,@ParamValue = '2,4,,,6,,8,,2,,4'
EXECUTE sp_executesql @SQL, @ParamDefinition, @P1 = @ParamValue

OUTPUT:

PK          RowValue
----------- --------
2           BB
4           DDDD
6           FF

(3 row(s) affected)

Answer 3

, . , :

SELECT * from table WHERE RegionCode in ('X101,B202')

SELECT * from table WHERE RegionCode in ('''X101'', ''B202''')

, RegionCode 'X101,B202' ''X101','B202'' ( ) .

:

Set @Cmd = N'SELECT * FROM table WHERE RegionCode in (@P1,@P2)'
SET @Code1 = 'X101'
SET @Code2 = 'B202'

If you have more than two items in this list, you can go a different route, perhaps with temporary tables or table parameters.