SUM(@Month1)

  • 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

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

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

  • 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)

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

    Thanks for the help...

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

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