none
Adding an optional parameter to User Defined Function

    Question

  • I tried this in an User Defined Function,
          SomeFunction(@param1 int, @op_param bit = 0 )

    When I call the function, SomeFunction(123, 1) --> it works.
    But if I omit the optional parameter  and call SomeFunction(123) , it throws Insufficient Argument exception. If I pass default for the optional parameter it works with the default value i.e.(0).
             This function is being used by 10 SP's (Stored Procedures) in the system. Only 1 SP needs to pass the additional parameter. In that case I have to go to each and everyone and do the change in all places (add a parameter).
    What has gone wrong here? Like to know any other solutions for these type of scenarios (parameter overloading) in SQL (MS SQL server 2005).
    Friday, May 22, 2009 10:16 AM

Answers

  • for UDF you cannot omit optional parameters. you can pass a value or 'default' keyword...
    if you want omit parameters, you have to use sp...

    a workaround could be another function with 1 parameter that call 2nd function with default:

    create function x (@a int, @b int = 0) returns int as
    begin
        return @a + @b
    end

    create function y (@a int) returns int as
    begin
        return dbo.x(@a, default)
    end
    Friday, May 22, 2009 10:21 AM

All replies

  • for UDF you cannot omit optional parameters. you can pass a value or 'default' keyword...
    if you want omit parameters, you have to use sp...

    a workaround could be another function with 1 parameter that call 2nd function with default:

    create function x (@a int, @b int = 0) returns int as
    begin
        return @a + @b
    end

    create function y (@a int) returns int as
    begin
        return dbo.x(@a, default)
    end
    Friday, May 22, 2009 10:21 AM
  • Thanks Lepaca.  




    Monday, May 25, 2009 6:12 AM
  • Thanks for the clarification.

    Funny though--they're not really optional parameters but rather optional parameter values :-)

     

     

    Thursday, July 28, 2011 1:40 PM