We help IT Professionals succeed at work.

t-sql need help on t-sql

114 Views
Last Modified: 2017-03-07
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.
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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

Author

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

SELECT CONVERT(int, Value ) FROM dbo.Split(@srvID , ',')
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Author

Commented:
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
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Scott Pletcher - thank you very much for your help.  that works.

Author

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...

Author

Commented:
thanks
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions