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

Answer 1

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

Answer 2

Thanks Lepaca.  

Answer 3

Thanks for the clarification.

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