Tales from the crypt: – Passing multiple int values into a variable

By | October 15, 2019

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