passing column name to stored procedure for avg function; returns cannot convert varchar to numeric error
Posted on 2005-03-01
I need to work out the average of particular columns in a table in my database. But, i need the column name to be set to a variable, thus allowing the user to select what column they want the average value of. I am encountering a problem when trying to do this, because the average function takes a numeric value in its argument and i have declared the column name as a varchar, as this is what it gets passed across as. Below is my stored procedure:
create procedure sp_CalcAvg
IN (SELECT HeadData.SerialNumber_ID
WHERE HeadData.Model = @model)
I am totally unsure how to do this, so that i am passing in the datatype of the column and not the column name itself. @col should be determined by the user as to which column they want to calculate the average of depending on the @model parameter. I have tried casting the @col parameter to a decimal, but to no avail...please help!!