Avatar of jfreeman2010
jfreeman2010
Flag for United States of America asked on

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)  ;
    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.
* T-SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Jim Horn

>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

jfreeman2010

ASKER
When I try this, still get the same error:

SELECT CONVERT(int, Value ) FROM dbo.Split(@srvID , ',')
Jim Horn

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jfreeman2010

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

RETURNS @Array TABLE (
   TokenID Int PRIMARY KEY IDENTITY(1,1),  
                                           
                                           
   Value Varchar(max))

AS

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

BEGIN

   DECLARE
      @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
      END
   END
   ELSE BEGIN
     
      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)
      END
   END
   
   RETURN

END

GO
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jfreeman2010

ASKER
Scott Pletcher - thank you very much for your help.  that works.
jfreeman2010

ASKER
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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jfreeman2010

ASKER
thanks
Scott Pletcher

You're welcome. I did get the points, but more importantly you got a good answer :).