sp_executesql with 'IN' statement
- by user300992
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?
