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.