t-sql need help on t-sql

I have a SQL getting error, see below test case.
Need Help on the second SQL, getting error (see below error message). Thank you very much

DECLARE @srvID                  varchar(100)
set @srvID = '2, 281';

SELECT cast(Value as smallint) srv_id FROM dbo.Split(@srvID , ',')

create table #tmp(      id int, SERVICE_ID smallint, First_Name varchar(100), last_Name varchar(100),SRV_PRICE smallmoney)  ;
      insert into #tmp values(49304, 2, 'John', 'Doe', 180.00);
    insert into #tmp values(49304, 281, 'John', 'Doe', 35.00);

FROM       #tmp
WHERE      id = 49304
      ( CASE
             WHEN cast( @srvID as smallint) = 0 THEN (  SERVICE_ID  )
            (  SELECT cast(Value as smallint) FROM dbo.Split(@srvID , ',') )
 drop table  #tmp;

The first select get result as:

the second select get the error:
Conversion failed when converting the varchar value '2, 281' to data type smallint.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
>the second select get the error:  Conversion failed when converting the varchar value '2, 281' to data type smallint.
That's because this line uses the variable in an expression CASTing to a smallint without converting it to numeric values

      ( CASE 
             WHEN cast( @srvID as smallint) = 0 THEN (  SERVICE_ID  )

Open in new window

jfreeman2010Author Commented:
When I try this, still get the same error:

SELECT CONVERT(int, Value ) FROM dbo.Split(@srvID , ',')
Jim HornSQL Server Data DudeCommented:
Where is [Value] defined in your code, and what data type is it?

Please move all your code into a code block, and also include the code for the table valued function dbo.Split
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

jfreeman2010Author Commented:
ALTER Function [dbo].[Split] (
   @InputText Varchar(max),  
   @Delimiter Varchar(10))  

   Value Varchar(max))


-- Function Split                                        --
--    • Returns a Varchar rowset from a delimited string --


      @Pos Int,        
      @End Int,        
      @TextLength Int,  
      @DelimLength Int  

   SET @TextLength = DataLength(@InputText)

   IF @TextLength = 0 RETURN

   SET @Pos = 1
   SET @DelimLength = DataLength(@Delimiter)

   IF @DelimLength = 0 BEGIN  
      WHILE @Pos <= @TextLength BEGIN
         INSERT @Array (Value) VALUES (SubString(@InputText,@Pos,1))
         SET @Pos = @Pos + 1
      SET @InputText = @InputText + @Delimiter
      SET @End = CharIndex(@Delimiter, @InputText)
      WHILE @End > 0 BEGIN
         INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End - @Pos))
         SET @Pos = @End + @DelimLength
         SET @End = CharIndex(@Delimiter, @InputText, @Pos)


Scott PletcherSenior DBACommented:
FROM       #tmp
WHERE      id = 49304
AND      (@srvID = '0' OR
      SERVICE_ID IN (  SELECT cast(Value as smallint) FROM dbo.Split(@srvID , ',')  )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfreeman2010Author Commented:
Scott Pletcher - thank you very much for your help.  that works.
jfreeman2010Author Commented:
Scott Pletcher  did result my insure and should got 500 points.  I am what I did close this without give him the point.  Very Sorry Scott.  I did not mean that bad...
jfreeman2010Author Commented:
Scott PletcherSenior DBACommented:
You're welcome. I did get the points, but more importantly you got a good answer :).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.