Tales From The Crypt: - Passing Multiple Int Values Into A Variable Misc

Reference Post

https://social.msdn.microsoft.com/Forums/en-US/7c04a296-3c26-4a43-ae63-bc90139f4ab3/error-converting-data-type-nvarchar-to-int?forum=sqlgetstarted

I was browsing through the forums and found this question being asked. While the reason for the error is fairly obvious I felt it needed further investigation so here it is.

The question being asked is why does the below query work

While the next one fails

Naturally anyone with any TSQL experience will know that in the second case we aren’t passing an int value anymore and therefore we get the error.

But the more meaningful question is how to achieve the required result in the case when we want to pass multiple inputs. While there are a number of ways the problem can be solved such as tokenizing the string using STRING_SPLIT or XML etc. these are solutions that work on specific versions of SQL Server or with developers who have the required string background to understand the root cause.

Here I provide the most basic solution that will work for the problem at hand.

As can be expected the CASE statement only returns result for the first condition that matches the requirement and I assume that part of the logic is supposed to be maintained as it is.

TSQL provided below for those looking to copy paste.

DECLARE @values INT = 100

SELECT CASE 
        WHEN @values = 100
            THEN 10000
        WHEN @values = 200
            THEN 2000
        ELSE 3000
        END GO

DECLARE @values VARCHAR(100) = '100, 200'

SELECT CASE 
        WHEN Patindex('%100%', @values) > 0
            THEN 10000
        WHEN Patindex('%200%', @values) > 0
            THEN 2000
        ELSE 3000
        END GO

DECLARE @values VARCHAR(100) = '10,200'

SELECT CASE 
        WHEN Patindex('%100%', @values) > 0
            THEN 10000
        WHEN Patindex('%200%', @values) > 0
            THEN 2000
        ELSE 3000
        END

Please Consider Subscribing