Skip to Content
avatar image
Former Member

Query

hi all,

i am using this query to convert inches to cm

Declare @a as nvarchar
Declare @b as nvarchar
Declare @c as nvarchar
Declare @d as nvarchar
Declare @e as nvarchar
Declare @f as nvarchar
Declare @g as nvarchar
Declare @h as nvarchar
set @a=convert(Numeric(10),(($[@SIZETABLE.U_LEN.number])/2.54))
set @b=convert(Numeric(10),(($[@SIZETABLE.U_width.number])/2.54))
set @c=convert(Numeric(10),(($[@SIZETABLE.U_hgt.number])/2.54))
set @d=convert(Numeric(10),(($[@SIZETABLE.U_dia.number])/2.54))
set @e=convert(Numeric(10),(($[@SIZETABLE.U_thick.number])/2.54))
set @f=convert(Numeric(10),(($[@SIZETABLE.U_bag.number])/2.54))
set @g=convert(Numeric(10),(($[@SIZETABLE.U_garment.number])/2.54))
set @h=convert(Numeric(10),(($[@SIZETABLE.U_size.number])/2.54))
SELECT  case when @a='' then '' else @a+'L' 
end + case when @b='0' then '' else 'x'+ @b
+'w' end+case when @c ='0' then '' else 'x'+@c
+'h' end+case when @d='0' then '' else 'x'+@d+
'd' end+case when @e='0' then '' else 'x'+@e+
't' end+case when @f='0' then '' else 'x'+@f+'b' end+
' '+rtrim($[@sizetable.U_unit]) + case when @g='0' then '' else ','+
@g+'g' end+case when @h='0' then '' else ','+
@h+'s' end

this query is working fine for numeric(10) but i want result upto 2 decimal place when i am convert it to numeric(10,2) it gives me error 'Arithmetic overflow error converting nvarchar to data type numeric'. plz suggest me.

thanks

Rahul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 01, 2011 at 09:38 AM

    Hi Rahul,

    try this:

    Declare @a as nvarchar
    Declare @b as nvarchar
    Declare @c as nvarchar
    Declare @d as nvarchar
    Declare @e as nvarchar
    Declare @f as nvarchar
    Declare @g as nvarchar
    Declare @h as nvarchar
    set @a=convert(nvarchar(50),(($[@SIZETABLE.U_LEN.number])/2.54))
    set @b=convert(nvarchar(50),(($[@SIZETABLE.U_width.number])/2.54))
    set @c=convert(nvarchar(50),(($[@SIZETABLE.U_hgt.number])/2.54))
    set @d=convert(nvarchar(50),(($[@SIZETABLE.U_dia.number])/2.54))
    set @e=convert(nvarchar(50),(($[@SIZETABLE.U_thick.number])/2.54))
    set @f=convert(nvarchar(50),(($[@SIZETABLE.U_bag.number])/2.54))
    set @g=convert(nvarchar(50),(($[@SIZETABLE.U_garment.number])/2.54))
    set @h=convert(nvarchar(50),(($[@SIZETABLE.U_size.number])/2.54))
    SELECT  case when @a='' then '' else @a+'L' 
    end + case when @b='0' then '' else 'x'+ @b
    +'w' end+case when @c ='0' then '' else 'x'+@c
    +'h' end+case when @d='0' then '' else 'x'+@d+
    'd' end+case when @e='0' then '' else 'x'+@e+
    't' end+case when @f='0' then '' else 'x'+@f+'b' end+
    ' '+rtrim($[@sizetable.U_unit]) + case when @g='0' then '' else ','+
    @g+'g' end+case when @h='0' then '' else ','+
    @h+'s' end
    

    Thanks,

    Neetu

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hello Rahul,

      You should check your all data type lenght ,like Nvarchar(10) means your resulting value should be in 10 charcter range for proper result.

      Thanks

      Manvendra Singh Niranjan

  • avatar image
    Former Member
    Jun 01, 2011 at 12:57 PM

    Hello Rahul,

    Why are you not using Decimal(10,2) ?

    Thanks

    Manvendra Singh Niranjan

    Add comment
    10|10000 characters needed characters exceeded