sp_executesql with 'IN' statement

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

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

However, when I use sp_executesql to execute the following, it doesn't return anything.

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

The is what I have 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 did I do wrong?

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

e.g. when @Code is X101,B202 then the query is just being run as: SELECT * FROM Table WHERE RegionCode IN ('X101,B202') So, it's looking for a RegionCode with the value that is contained with @P1. Even when you include single quotes, all that means is the value it searches for in RegionCode is expected to contain those single quotes.

You'd need to actually concatenate the @Code variable into the @Cmd sql command text in order for it to work the way you are thinking:

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

Obviously though, this just opens you up to SQL injection so you'd need to be very careful if you took this approach to make sure you guard against that.

There are alternative ways of dealing with this situation where you want to pass in a dynamic list of values to search for.

Check out the examples on my blog for 2 approaches you could use with SQL Server 2005. One involves passing in a CSV list in the form "Value1,Value2,Value3" which you then split out into a TABLE variable using a user defined function (there's a lot of mentions of this approach if you do a quick google or search of this site). Once split out, you then join that TABLE var in to your main query. The second approach is to pass in an XML blob containing the values and use the built-in XML functionality of SQL Server. Both these approaches are demonstrated with performance metrics in that link, and they require no dynamic SQL.

If you were using SQL Server 2008, Table Value Parameters would be the way to go - that's the 3rd approach I demonstrate in that link which comes out best.

There are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

You need to create a split function. This is how a split function can be used:

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

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

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)

Once the Numbers table is set up, create this split function:

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 

You can now easily split a CSV string into a table and join on it or use it however you need, even from within dynamic sql. Here is how to use it in the dynamic parameterized query from your question:

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

HERE is a working sample to try out (must have the numbers table and split function setup first):

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)

It looks like the problem is the single parameter. In effect, you're ending up with:

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

or

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

That is, RegionCode must be equal to 'X101,B202' or ''X101','B202'' (the complete string) to work.

You're best bet is to use two parameters here:

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

If you're going to have more than two items in that list though, you may want to go another route, probably with temp tables or table-valued parameters.