Post reply

create aggregation with 2 parameters

  • April 29, 2010 at 10:04 am

    #386819

    Hi, Has anyone made a custom aggregation function with more than 1 parameter? From BOL it apears to be possible.... i.e

    CREATE AGGREGATE [ schema_name . ] aggregate_name

    (@param_name <input_sqltype>

    [ ,...n ] )

    RETURNS <return_sqltype>

    EXTERNAL NAME assembly_name [ .class_name ]

    <input_sqltype> ::=

    system_scalar_type | { [ udt_schema_name. ] udt_type_name }

    <return_sqltype> ::=

    system_scalar_type | { [ udt_schema_name. ] udt_type_name }

    ....... seems to suggest i can have n @param_name's.

    But when i run

    CREATE AGGREGATE MyAggregate (@Input1 INT, @Input2 INT)

    RETURNS INT

    EXTERNAL NAME MyAggregate.MyAggregate

    GO

    I get

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near ','.

    I can't really see why, but can't find any examples online of where people have used more than one parameter.

    For context, i'm trying to write a function that takes an x & y parameter, a sql version of the SLOPE() function in excel.

    Thanks

    Bob.

  • May 24, 2010 at 6:49 am

    #1171516

    I think having multiple input parameters was a new function of SQL Server 2008 and 2005 is limited to a single parameter.

    SQL Server 2005 CREATE AGGREGATE (BOL)

    @param_name

    Is a parameter in the user-defined aggregate. The value of the parameter must be supplied by the user when the aggregate function is executed. Specify a parameter name by using an at sign (@) as the first character. The parameter name must comply with the rules for identifiers. Parameters are local to the function.

    SQL Server 2008 CREATE AGGREGATE (BOL)

    @param_name

    One or more parameters in the user-defined aggregate. The value of a parameter must be supplied by the user when the aggregate function is executed. Specify a parameter name by using an "at" sign (@) as the first character. The parameter name must comply with the rules for identifiers. Parameters are local to the function.

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

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