November 20, 2013 at 4:26 pm
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
November 20, 2013 at 5:00 pm
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.
November 20, 2013 at 5:06 pm
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
November 20, 2013 at 5:07 pm
just numeric values as far as I am aware and yes it is a nvarchar column
productname Total Licensable
.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
November 20, 2013 at 5:12 pm
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
November 20, 2013 at 5:14 pm
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.
November 20, 2013 at 5:36 pm
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
November 20, 2013 at 5:46 pm
November 20, 2013 at 5:46 pm
I am even more confused now as that just returns a list of databases
November 20, 2013 at 5:51 pm
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'
November 20, 2013 at 5:57 pm
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
productname productversion softwaremanufacturer type category installations Licensable
Office Proof 2010 999.x Microsoft Commercial Office Productivity 145 Non Licensable
Office Proof 2010 999.x Microsoft Commercial Office Productivity 145 Non Licensable
Office Proof 2010 999.x Microsoft Commercial Office Productivity 145 Non Licensable
Office Proof 2010 999.x Microsoft Unidentified Not Assigned 3 Non Licensable
Office Proof 2010 999.x Microsoft Unidentified Not Assigned 3 Non Licensable
Office Proof 2010 999.x Microsoft Unidentified Not Assigned 3 Non Licensable
Office Proof 2010 999.x Microsoft Unidentified Not Assigned 1 Non Licensable
Office Proof 2010 999.x Microsoft Unidentified Not Assigned 1 Non 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
softwaremanufacturer productname productversion FirstTotal Total Licensable
Microsoft Office Proof 2010 999.x 446 149 Non Licensable
November 20, 2013 at 6:02 pm
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