Does int in (SELECT varchar...) work?

I'm trying to modify some code written bey somebody else that is generating a model for a report being built in Business Intelligence Development Studio (Microsoft Reporting Services).

I was wondering if this is necessary?

WHERE (convert(varchar,R.Kommunenr) COLLATE DATABASE_DEFAULT IN (SELECT Item FROM dbo.Split('"+ @kommune+ "', ',') AS Split_1))"

$kommune is a text built up like this: "kommune1,ko开发者_运维问答mmune2,kommune3,..."

Is it okay to check that an int (R.kommunenr) is IN (SELECT Item FROM dbo.Split('"+ @kommune+ "', ',') AS Split_1) which I reckon is a varchar. Like this

WHERE (R.Kommunenr IN (SELECT Item FROM dbo.Split('"+ @kommune+ "', ',') AS Split_1))

The problem is I can't run the code, as I atm only have the code, and not permission to run it.

In TSQL implicit conversions, VARCHAR are cast into INT and not vice versa.

So your second query will fail if r.kommunenr is an INT and dbo.split returns a VARCHAR not castable into an INT.

If you want to benefit from an index on r.kommunenr, you may try this:

SELECT  *
FROM    r
WHERE   kommunenr IN
        (
        SELECT  CASE IsNumeric(item)
                WHEN 1
                THEN
                        CASE
                        WHEN CAST(item AS FLOAT) = CAST(CAST(item AS FLOAT) AS INT)
                        THEN
                                CAST(CAST(item AS FLOAT) AS INT)
                        END
                END
        FROM    dbo.split(@kommune) split1
        )

This will handle edge cases like 1E8 which is numeric but not directly castable to INT.