Is it possible to SUM(@variable)?

Question

Is it possible to SUM(@variable)?

commonman00

Old Hand

Points: 345
More actions
April 17, 2012 at 10:36 am

#254321

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 the @MeasureName variable 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 suggest how this can be accomplished?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Build Dynamic SQL inside of cursor and execute it.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

commonman00 Old Hand Points: 345 More actions · Answer 2

commonman00

Old Hand

Points: 345

April 17, 2012 at 10:46 am

#1474893

Any examples?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

DECLARE @sql NVARCHAR(4000)

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

SET @sql = 'select SUM(' + @MeasureName + ') from Sales'

EXEC sp_executesql @sql

....

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

Or even better get rid of that cursor entirely. There does not appear to be any reason for a cursor from what you have posted. If you want some help to get your results set based (and a LOT faster), post the ddl and sample data along with desired output. Take a look at the first link in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

commonman00 Old Hand Points: 345 More actions · Answer 5

Thanks guys for your pointers.

Managed to setup dynamic SQLs with tips from http://www.sommarskog.se/dynamic_sql.html.

Also managed to get rid of the cursor with tips from http://www.sql-server-performance.com/2004/operations-no-cursors/

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

commonman00 (4/18/2012)
Thanks guys for your pointers.
Managed to setup dynamic SQLs with tips from http://www.sommarskog.se/dynamic_sql.html.
Also managed to get rid of the cursor with tips from http://www.sql-server-performance.com/2004/operations-no-cursors/

Just curious which method suggested on that link you used to get rid of your cursor? I see the suggestion to replace it with a loop. That really isn't any better than a cursor.