Conversion failing

  • Hi Professionals

    I have a procedure which when I execute it I get the error

    Msg 245, Level 16, State 1, Procedure totalinstallations, Line 9

    Conversion failed when converting the nvarchar value 'installations' to data type int.

    here it is

    ALTER procedure [dbo].[totalinstallations]

    @importedquery nvarchar(1000),

    @coltotal nvarchar(1000)

    AS

    BEGIN

    SELECT @importedquery,

    SUM( DISTINCT CAST( @coltotal AS int)) as Total ,

    Licensable

    FROM newtable

    GROUP BY ProductName,

    ProductVersion,

    SoftwareManufacturer,

    Licensable

    END

    exec totalinstallations 'productname','installations'

    Yet when I run this manually like so it works fine

    SELECT productname,

    SUM( DISTINCT CAST( installations AS int)) as Total ,

    Licensable

    FROM newtable

    GROUP BY ProductName,

    ProductVersion,

    SoftwareManufacturer,

    Licensable

    any ideas

  • In your first example you are passing the text value of 'installations' in to your input parameter @coltotal and then trying to cast that to an integer which will fail since it is not a numeric value

    In your second example, the values you are casting in to an integer are coming from a table called newtable and column installations. I am guessing that in that table, the values are either null or numeric values (even if the datatype of column installations is nvarchar)

    You would need to tell us the values that are in that newtable.

  • To make it simpler, your first query you are doing the same as

    select SUM( DISTINCT CAST('installations' AS int)) as Total

    It almost looks like by your question that you want to pass in the column name of the new table upon which you want to do a sum of those column values but it is not possible to do it that way. To get around this you coul look at executing dynamic sql although I'm not a huge fan of this and avoid it where possible. You could also get around it with some case statements such as

    select

    case @column

    when 'column1' then SUM(column1)

    when 'column2' then SUM(column2)

    when 'column3' then SUM(column3)

    from newtable

  • just numeric values as far as I am aware and yes it is a nvarchar column

    productname TotalLicensable

    .NET Framework 132 Non Licensable

    .NET Framework 14 Non Licensable

    .NET Framework Client Profile 36 Non Licensable

    .NET Framework Extended 35 Non Licensable

    .NET Framework Service Pack 15 Non Licensable

  • trouble is I never know what the column is going to be called as this is created from an imported spreadsheet

    also never going to be sure if the column in question would hold only numeric values so is there a way to just sum up values only if that column only hold numeric values?

    or am I just being completely bonkers

  • So there is your issue. In the stored procedure that gives you an error, you are trying to convert the text string 'installations' in to an integer which gives you the error. It is not passing a column name in to the query like you think. You cannot pass column names as variables like that.

  • I guess you can go down the dynamic sql then

    e.g.

    declare @columnName varchar(128)

    set @columnName = 'name'

    exec ('select ' + @columnName + ' from sys.databases')

    As for your second question, yes there is a function called try_convert. This "Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null."

    http://technet.microsoft.com/en-us/library/hh230993.aspx

    Its exactly what you want, problem for you though is it was introduced in SQL 2012 and I am guessing by the forum you are in, you are on SQL 2008/2008 R2

  • Actually, you should be able to use isnumeric() function to get what you want to do....

    e.g.

    declare @test-2 table (col1 varchar(50))

    insert into @test-2 values ('55'),('60A'),('45'),('AA')

    select sum(case when isnumeric(col1)=1 then convert(int, col1) else 0 end)

    from @test-2

  • I am even more confused now as that just returns a list of databases

  • Apologies. I was just querying the sys.databases table to demonstrate how you would call dynamic sql. Yours would be something more like...

    ALTER procedure [dbo].[totalinstallations]

    @importedquery nvarchar(1000),

    @coltotal nvarchar(1000)

    AS

    BEGIN

    EXEC ('

    SELECT ' + @importedquery + ',

    SUM( CASE WHEN IsNumeric(' + @coltotal + ')=1 THEN CAST( ' + @coltotal + ' AS int) ELSE 0 END) as Total ,

    Licensable

    FROM newtable

    GROUP BY ProductName,

    ProductVersion,

    SoftwareManufacturer,

    Licensable')

    END

    exec totalinstallations 'productname','installations'

  • ok sort of works but that totals up everything and not the distinct columns, it still totals the duplicates, the correct total should be 149

    EG

    productnameproductversionsoftwaremanufacturertypecategoryinstallationsLicensable

    Office Proof 2010999.xMicrosoftCommercialOffice Productivity145Non Licensable

    Office Proof 2010999.xMicrosoftCommercialOffice Productivity145Non Licensable

    Office Proof 2010999.xMicrosoftCommercialOffice Productivity145Non Licensable

    Office Proof 2010999.xMicrosoftUnidentifiedNot Assigned3Non Licensable

    Office Proof 2010999.xMicrosoftUnidentifiedNot Assigned3Non Licensable

    Office Proof 2010999.xMicrosoftUnidentifiedNot Assigned3Non Licensable

    Office Proof 2010999.xMicrosoftUnidentifiedNot Assigned1Non Licensable

    Office Proof 2010999.xMicrosoftUnidentifiedNot Assigned1Non Licensable

    SELECT softwaremanufacturer,productname,productversion,

    sum(case when isnumeric(installations)=1 then convert(int, installations) else 0 end) as FirstTotal,

    SUM( DISTINCT CAST( installations AS int)) as Total ,

    Licensable

    FROM newtable

    GROUP BY ProductName,

    ProductVersion,

    SoftwareManufacturer,

    Licensable

    order by FirstTotal desc

    which shows

    softwaremanufacturerproductnameproductversionFirstTotalTotalLicensable

    Microsoft Office Proof 2010999.x 446 149Non Licensable

  • So take the working sql you have and convert it in to dynamic passing in the column names. You may face trouble it the number of columns change though. It is hard if you never know what the columns are going to be called

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply