Is it possible to SUM(@variable)?

Question

Is it possible to SUM(@variable)?

Viewing 7 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.