Conversion failed when converting the varchar value '98.97%' to data type int

Here is my report. The line for set @percent is the issue and is returning the following error

Conversion failed when converting the varchar value '98.97%' to data type int.

Code:

declare @compliant      int
declare @notcompliant   int
declare @total          int
declare @percent        int

set @compliant = (select [# of patches] from vpatchcompliancecount where Status = 'compliant')

set @notcompliant = (select [# of patches] from vpatchcompliancecount where Status = 'not compliant')

set @total = (select SUM([# of Patches]) from vpatchcompliancecount)

set @percent = CONVERT(varchar,convert(DECIMAL (4,2),100.0 * 
(select [# of Patches] from vpatchcompliancecount where Status = 'compliant')/(select SUM([# of Patches]) from vpatchcompliancecount))) + '%'

select @compliant [Installed Patches], @notcompliant [Pending], @total [Total], @percent [Compliance %]

Answers


The data type int can only contain whole numbers. Converting '98' to int would work, but '98.97' does not work. You should declare your @Percent variable as a Decimal or Number data type.


Your declaration is wrong, it should be:

DECLARE    @percent    VARCHAR

Though personally I'd use a group by, and calculate the results as follows (this has not been error/syntax checked):

SELECT
    [Installed Patches] = SUM(CASE WHEN Status = 'compliant' THEN [# of patches] ELSE 0 END)
    ,[Pending] = SUM(CASE WHEN Status = 'not compliant' THEN [# of patches] ELSE 0 END)
    ,[Total] = SUM[# of Patches]
    ,[Compliance %] = CONVERT(VARCHAR,
        (   100.0 * 
            (   SUM(CASE WHEN Status = 'compliant' THEN [# of patches] ELSE 0 END))
                / (SUM([# of patches]) 
            )
        )
    ) + '%'
FROM 
    vpatchcompliancecount

You are trying to set 98.97% to an integer. Integers only store whole numbers. 98 would be an integer. You would need to put the percentage into a varchar in order to include the percent sign.


Need Your Help

How to insert array from another array using PHP?

php arrays codeigniter

Hello guys I need a little help here about arrays.

css skew transformation in IE 7 & 8

html css html5 internet-explorer css3

I have rhombus styled menu which uses the following code. It works fine in Chrome, firefox, ie9 etc but i now need to get this working in IE 7 & 8! :( Is there a way to do this on these legacy