Use SELECT AVG with parameters "SELECT AVG(@parameter)" SQL!!

Question

I'm working with a table of float values, but I enter the column by parameter

SELECT AVG(@var) AS Aver FROM ListVal

When I enter the column name (the parameter) I recieve an error: That the datatype nvarchar is not valid for the avg operator. But all the columns are float.

Help!!! please

Answer 1

You have to use dynamic SQL

e.g.

EXEC( 'SELECT AVG( [' + @var + '] ) AS Aver FROM ListVal' )

Answer 2

Query parameters substitute for a single literal value, not a column name. This is standard SQL behavior, supported the same in every brand of RDBMS.

So you supplied the name of a column and it's as if you ran this query:

SELECT AVG('columnname') ...

Which is a meaningless operation. What's the AVG() of a string?

@Matt Rogish is correct in his answer that the only way to make column names (or table names, or other SQL syntax) dynamic is to use dynamic SQL. That is, interpolate application variables into a string, and then use the resulting string as an SQL query.

You can use query parameters only to inject a single scalar value, and that's how the parameter will be interpreted in the query.