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