Using Variables in Aggregate Functions??

[charliescott]

I'm fairly certain there is a very quick and simple solution to the following issue that I'm having...


USE Northwind

DECLARE @COLUMN_NAME VARCHAR(50)
SELECT @COLUMN_NAME='LastName'

--Following result = 8 (Represents the actual length of string 'LastName'

SELECT MAX(LEN(@COLUMN_NAME)) FROM Employees

--Expected / Correct result = 9 (Represents the max length for actual values stored in LastName field of Employees table)

SELECT MAX(LEN(LastName)) FROM Employees

 

[gmmastros]

One method...

DECLARE @COLUMN_NAME VARCHAR(50)
SELECT @COLUMN_NAME='LastName'

[!]DECLARE @SQL VarChar(8000)
Set @SQL = '[/!]
SELECT MAX(LEN([!]' + @COLUMN_NAME + '[/!])) FROM Employees[!]'[/!]

[!]Exec (@SQL)[/!]

This method is called dynamic SQL and certainly has its drawbacks regarding security and performance. Do a little research before using this method.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom