troubleshooting Question

t-sql need help on t-sql

Avatar of jfreeman2010
jfreeman2010Flag for United States of America asked on
* T-SQLMicrosoft SQL Server
9 Comments1 Solution123 ViewsLast Modified:
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)  ;
    GO  
      insert into #tmp values(49304, 2, 'John', 'Doe', 180.00);
    insert into #tmp values(49304, 281, 'John', 'Doe', 35.00);
   
    GO
 

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



The first select get result as:
srv_id
2
281

the second select get the error:
Conversion failed when converting the varchar value '2, 281' to data type smallint.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros