Is it possible to SUM(@Variable)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is it possible to SUM(@Variable)

Author

Topic

Commonman00
Starting Member

1 Post

Posted - 2012-04-17 : 12:33:21

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:01:06

as i understand you're having column name as value in the field. so if you want sum() to applied over actual field then you need to use dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-18 : 04:33:26

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-27 : 08:53:40

Please post some sample data. Its hard to understand what you are trying to do without sample data.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

pphillips001
Starting Member

23 Posts

Posted - 2012-04-30 : 05:08:47

Commonman00

As you are using a cursor, you have to treat the sum programatically.

Declare another variable before the loop ie DECLARE @MySum int

in the loop, you put something like SET @MySum = @MySum + (whatever value the cursor is adding up)

After the loop you can see it with SELECT @MySum.

Hope this makes sense.

Paul

===========================
There is no reality - only perspective.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources