I am thinking of setting up a control table which has 2 columns: - MeasureID - MeasureName
The MeasureName corresponds to the column name of another table and I want to loop through the control table, then inserting the various measures into a summary table
I then setup a cursor: DECLARE iCursor as CURSOR FOR SELECT MeasureID, MeasureName FROM ControlTable
OPEN iCursor FETCH NEXT from iCursor into @MeasureID, @MeasureName
WHILE (@@FETCH_STATUS = 0) BEGIN
--This is where I am having problem select SUM(@MeasureName) from Sales
The value of @MeasureName is SalesAmt which is a column in the Sales table. I keep getting error: Operand data type varchar is invalid for sum operator. I think that's because SQL is trying to sum the MeasureName field instead of SalesAmt.
Is it possible to pass a variable to the SUM function? The Left(@MeasureName, 3) correctly returns 'Sal' but for some reasons SUM(@MeasureName) is not translated to SUM(SalesAmt). Can anyone help?