Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

passing column name to stored procedure for avg function; returns cannot convert varchar to numeric error

Hi all,

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
(
    @col varchar(20),
    @model varchar(20)
)

AS

SELECT AVG(@col)
FROM ParameterChecks
WHERE ParameterChecks.SerialNumber_ID
IN (SELECT HeadData.SerialNumber_ID
    FROM HeadData
    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!!
0
mitesh114
Asked:
mitesh114
  • 2
1 Solution
 
adatheladCommented:
You need to use dynamic sql for this:
create procedure sp_CalcAvg
(
    @col varchar(20),
    @model varchar(20)
)
AS
DECLARE @strSql VARCHAR(1000)
SET @strSql = 'SELECT AVG(' + @col + ')
FROM ParameterChecks
WHERE ParameterChecks.SerialNumber_ID
IN (SELECT HeadData.SerialNumber_ID
    FROM HeadData
    WHERE HeadData.Model = ' + '''' + @model + '''' + ')'
EXECUTE(@strSql)
0
 
mitesh114Author Commented:
Thank you!!  I've been trying to get an answer to this for days...nobody else's answer seems to make sense!  If you could just clear up a few things for me though; what does (' + @col + ') and '''' mean??
0
 
adatheladCommented:
The best thing would be for you to change EXECUTE with PRINT temporarily - this will print out the statement  that is built up and being executed. Run it in query analyser and it should become clear what is going on.

Basically,
 (' + @col + ')  - this appends the name of the column into the SQL statement
i.e. assuming @col = 'MyColumn', then the SQL query will read: "SELECT AVG(MyColumn)...."

'''' - this just makes sure that the model parameter has single quotes around.

e.g. the fully built up SQL statement being executed will look something like:
SELECT AVG(MyColumn)
FROM ParameterChecks
WHERE ParameterChecks.SerialNumber_ID
IN (SELECT HeadData.SerialNumber_ID
    FROM HeadData
    WHERE HeadData.Model = 'SomeModel')

Hope this helps
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now