Optional parameters in SQL UDF without DEFAULT keyword [duplicate]

Question

I am looking for a solution, how to create SQL UDF with optional params.

Pseudocode for function where Param1 is necessary and Param2 may be filled (but not needed):

dbo.myFnc(Param1 int [, Param2 int])

Is there a way to create thislike function? For existing built-in sample watch the STR function

STR ( float_expression [ , length [ , decimal ] ] )

Answer 1

You need to pass all arguments to functions, unlike stored procedures. You can use the default keyword to indicate that default value is to be used for the parameter, rather than a user-specified value.

So you can create your function like this:

CREATE FUNCTION dbo.myFnc
(
 @param1 int,
 @param2 int)

...

and then call the function like this:

dbo.myFnc(Param1, default)