SUM(@Month1)

• jvanhoof

  SSC Enthusiast

  Points: 177

  More actions

  August 8, 2003 at 8:40 am

  #101612

  I am trying to use a variable in a select statement using a sum ... here is the code:

  SELECT TOP 100 PERCENT PCTR, SUM(@Month1) AS [Month]

  FROM dbo.kc_Bud_EssbaseM3

  WHERE (@Month1 <> 0) OR (@Month1 IS NOT NULL)

  GROUP BY PCTR

  ORDER BY PCTR

  The @Month1 has 'Jan' in it, but on execution SQL reports "The sum or average aggregate operation cannot take a varchar data type as an argument." ...

  Can someone help please ... I know the answer must be simple, but you help would be great ...

  John

• tran008

  Ten Centuries

  Points: 1002

  More actions

  August 8, 2003 at 8:58 am

  #469199

  You cannot added 'character' in you cased 'Jan'. Either convert this to numeric or cast it to Integer type.

• jvanhoof

  SSC Enthusiast

  Points: 177

  More actions

  August 8, 2003 at 8:59 am

  #469201

  The field name is 'Jan' that I want a sum for... it is a float field but called Jan

• DavidT

  SSCommitted

  Points: 1727

  More actions

  August 8, 2003 at 9:40 am

  #469212

  You cannot pass a field name into a select using a variable.

  One solution would be to pass it in to a dynamic select statement:

  declare @sql varchar(8000)

  set @SQl =

  'SELECT TOP 100 PERCENT PCTR, SUM(' + @Month1 + ') AS [Month]

  FROM dbo.kc_Bud_EssbaseM3

  WHERE (' + @Month1 + ' <> 0) OR (' + @Month1 + ' IS NOT NULL)

  GROUP BY PCTR

  ORDER BY PCTR'

  exec (@sql)

• jvanhoof

  SSC Enthusiast

  Points: 177

  More actions

  August 8, 2003 at 10:27 am

  #469220

  With this select statement using EXEC how would I get the result back into another variable ??

  Thanks for the help...

• bobsterboy

  SSCommitted

  Points: 1539

  More actions

  August 10, 2003 at 11:48 pm

  #469330

  The only way I know of returning a value back from dynamic sql is to use the system sp called sp_executesql. Here's an example:

  DECLARE @nvch500SQLStr as nvarchar(500),

  @nvch500ParmStr as nvarchar(500),

  @nvch20SegValLocal as nvarchar(20)

  --This is the select Statement and allows you to use Dynamic SQL AND also return a value from the statement

  SET @nvch500SQLStr = N'SELECT @nvch20SegValOUT = CategoryName FROM Northwind.dbo.categories WHERE CategoryID = 1'

  --This is where you declare your variables so you can pass data OUT from the Dynamic SQL Script

  SET @nvch500ParmStr = N'@nvch20SegValOut as nvarchar(20) OUTPUT'

  -- To run the SQLStr, you pass the SQLString and then the parameters and then map the SQLString

  -- parameters to your local parameters - you will want to tag your local parameters with Local

  -- to make it less confusing, but you could use any name for the mapping, just make sure the

  -- data types are the same.

  EXECUTE sp_executesql@nvch500SQLStr,

  @nvch500ParmStr,

  @nvch20SegValout = @nvch20SegValLocal OUTPUT

  print @nvch20segvalLocal