CODEWITHSUNDEEP
sqlsql-servert-sql
Moudiz
Moudiz

Reputation: 7377

SQL case condition with error failed converting

I have this query that causes this error:

Failed when converting the varchar value ')' to data type tinyint.

This is my code in question:

select  
    case t.name 
       when 'numeric' 
          then cast (c.precision + ')' as varchar(100)) 
    end 
from 
    sys.columns c 
inner join 
    sys.types t ON t.user_type_id = c.user_type_id
inner join 
    sys.tables tb on tb.name = 'EX_EMPLOYEE' 
where  
    c.name = 'B_CODE' and tb.object_id = c.object_id

Upvotes: 1

Views: 61

Answers (4)

holder
holder

Reputation: 585

I think you need to cast the tinyint value to a varchar before concatenating them. Like this (or just cast the c.precision as varchar(100) and then add the ')':

    select  
    case t.name when 'numeric' then cast (cast (c.precision as varchar(10)) +')' as varchar(100)) 
    end 

    from sys.columns c inner JOIN sys.types t ON t.user_type_id = 
    c.user_type_id
    inner join sys.tables tb on tb.name='EX_EMPLOYEE' where  c.name = 
    'B_CODE' and tb.object_id = c.object_id

Upvotes: 1

Matt
Matt

Reputation: 15061

Move your concatenation to outside the CAST, also changed JOIN and WHERE parameters around.

SELECT CASE WHEN t.name = 'numeric' 
            THEN CAST(c.precision AS varchar(100)) +')'
       END
FROM sys.columns c 
INNER JOIN sys.types t ON t.user_type_id = c.user_type_id
INNER JOIN sys.tables tb ON tb.object_id = c.object_id
WHERE c.name = 'B_CODE'
AND tb.name = 'EX_EMPLOYEE'

Upvotes: 1

Ivan Sivak
Ivan Sivak

Reputation: 7498

You can also try this:

select  
     case t.name when 'numeric' then cast (c.precision as varchar(100)) +')' end

as c.precision is obviously a tinyint data type. It fails on c.precision +')' as you're adding different data types.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

Perhaps (if 2012+)

case t.name when 'numeric' then concat(c.precision,')') end

Upvotes: 2

Related Questions