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?

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  April 17, 2012 at 10:40 am

  #1474886

  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

  April 17, 2012 at 10:46 am

  #1474893

  Any examples?

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  April 17, 2012 at 10:50 am

  #1474896

  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: 286602

  More actions

  April 17, 2012 at 11:56 am

  #1474930

  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

  April 18, 2012 at 12:52 am

  #1475175

  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: 286602

  More actions

  April 18, 2012 at 7:50 am

  #1475345

  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.

  _______________________________________________________________

  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/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 18, 2012 at 9:20 am

  #1475410

  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/

  Forum etiquette would have you post your solution to your problem. It may help others with a similar issue.