Case in where clause

Try using the below piece of transaction to solve your problem :

declare @userid varchar(100)
--set @userid='''109453'',''109454'''
set @userid = ''''
declare @sql varchar(200)
set @sql = (CASE WHEN @userid <>'''' THEN 'select * from Table_USERS
 where userid in(' + @userid + ')' ELSE 'select * from Table_USERS' END)
exec (@sql)

userid in

(CASE WHEN @userid <>''
 THEN cast(@userid as varchar(20)) 
ELSE userid END)

userid is string but your case statement returns a varchar hence the issue.
You need to ensure your case statement returns an int, so cast the WHEN clause output into an int.

Use split function, splitting by ","
Below is SQL Function for splitting by "," to a parameter
==================================
USE [EBS_Local]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 12/15/2011 16:57:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,bhavik>
-- Create date: <create>
-- Description: <split string="">
-- =============================================
ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) --'1,2,3,4,5',','
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
=============================

Use this function now write query

Select * from tableName where userid in (Select items from dbo.split(@UserID))

it will solve your problem.