Converting failure in SQL Server

Question

Possible Duplicate:
using dynamic IN clause in MSSQL

I have one SQL statement which includes parameter such as @StartRow, @StartEnd, @CategoryIds.

SELECT * FROM StackOverFlow
WHERE
StartRow = @StartRow
AND StartEnd = @StartEnd
AND CategoryId IN (@CategoryIds)

When I set the parameters like below:

DECLARE  @StartRow INT = 1;
DECLARE  @StartRow INT = 10;
DECLARE  @CategoryId NVARCHAR(50);
set @CategoryId ='124,125'

The failed message is:

Conversion failed when converting the nvarchar value '124,125' to data type int.

When I manually set the SQL where statement like below:

WHERE
StartRow = @StartRow
AND StartEnd = @StartEnd
AND CategoryId IN (124, 125)

It works.

How can I solve that converting problem?

You cannot do it that way, you must use dynamic SQL. No time to write up how right now, but you can look it up. Also look up the pitfalls of dynamic SQl. — HLGEM, Dec 04 '12 at 15:45

score 3 · Accepted Answer · answered Dec 04 '12 at 15:52

You need to use dynamic SQL for this. So you would write

DECLARE  @StartRow INT = 1;
DECLARE  @StartRow INT = 10;
DECLARE @CatogoryIds NVARCHAR(50);
SET @CatogoryIds = N'124, 125';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
    SELECT * 
    FROM StackOverFlow
    WHERE
        StartRow = ' + @StartRow + 
      N' AND StartEnd = ' + @StartEnd + 
      N' AND CategoryId IN (' + @CategoryIds + N');';
EXEC(@SQL);
GO

I hope this helps.

GarethD · Answer 2 · 2012-12-04T17:28:20.540

If using Dynamic SQL you should use parameterised queries and SP_EXECUTESQL like so

CREATE TABLE T (StartRow INT, StartEnd INT, CategoryID INT);
INSERT T VALUES (1, 10, 124), (1, 10, 125), (1, 10, 126);

DECLARE  @StartRow INT = 1;
DECLARE  @StartEnd INT = 10;
DECLARE  @CategoryId NVARCHAR(50) ='124); DROP TABLE T; --';

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT * 
            FROM    T 
            WHERE   StartRow = @Start
            AND     StartEnd = @End
            AND     CategoryID IN (' + @CategoryId + ')'

EXECUTE SP_EXECUTESQL @SQL, N'@Start INT, @End INT', @StartRow, @StartEnd;

Dynamic SQL Example

You can do this without dynamic SQL using SQL-Server's XML extensions, although I am not sure you necessarily want to, you may want to run some tests to check performance.

DECLARE  @StartRow INT = 1;
DECLARE  @StartEnd INT = 10;
DECLARE  @CategoryId NVARCHAR(50) ='124,125';


DECLARE @X XML = CAST(N'<root><catid>' + REPLACE(@CategoryID, ',', '</catid><catid>') + '</catid></root>' AS XML);

SELECT  StartRow,
        StartEnd,
        CategoryID
FROM    T
        INNER JOIN
        (   SELECT  [CatID] = cat.value('.', 'int')
            FROM    @X.nodes('/root/catid') c (cat)
        ) c
            ON c.CatID = CategoryID;

Example using XML Extension

The query basically converts your comma delimted string into XML, then splits the xml into it's own table.

This should also reduce the risk of sql injection as any malformed strings will throw an error rather than running. Consider these variables:

DECLARE  @StartRow INT = 1;
DECLARE  @StartEnd INT = 10;
DECLARE  @CategoryId NVARCHAR(50) ='124,125); DROP TABLE T; --';

Running with the XML extensions method will give this

Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the nvarchar value '124,125); DROP TABLE T; --' to data type int.

SQL Injection Fail with XML Extensions

Running with Dynamic SQL will give the desired results, however next time you come to run it you will get:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'T'.

SQL Injection Example

There are other methods of splitting strings rather than the XML method used above, however it was merely to demonstrate dynamic SQL is not the only way forward, and if you have no control of your input strings then it would be more sensible not to use it as malicious strings can corrupt your data!

For the sake of completeness of the answer you can also use table valued parameters. e.g.

CREATE TYPE dbo.IntegerList AS TABLE (value INT);

CREATE PROCEDURE dbo.ExampleProcedure @StartRow INT, @StartEnd INT, @CategoryID dbo.IntegerList READONLY
AS
BEGIN

    SELECT  *
    FROM    T 
    WHERE   StartRow = @StartRow
    AND     StartEnd = @StartEnd
    AND     CategoryID IN (SELECT Value FROM @CategoryID)
END

Converting failure in SQL Server

2 Answers2