CODEWITHSUNDEEP
sql-servert-sql
Brian
Brian

Reputation: 6071

TSQL Passing a varchar variable of column name to SUM function

I need to write a procedure where I have to sum an unknown column name.

The only information I have access to is the column position.

I am able to get the column name using the following:

 SELECT @colName = (SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TABLENAME' AND ORDINAL_POSITION=@colNum)

I then have the following: 

 SELECT @sum = (SELECT SUM(@colName) FROM TABLENAME)

I then receive the following error:

Operand data type varchar is invalid for sum operator

I am confused about how to make this work. I have seen many posts on convert and cast, but I cannot cast to an float, numeric, etc because this is a name.

I would appreciate any help.

Thank you

Upvotes: 1

Views: 3808

Answers (1)

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

This is not possible. You will have to assemble the SQL query and then execute it. Something like this:

SET @sqlquery = 'SELECT SUM(' + @colName + ') FROM TABLENAME'
EXECUTE ( @sqlquery )

Adjust accordingly.

Upvotes: 5

Related Questions