SQL Function and default values

Question

I've got a SQL Function which has 3 parameters. The 3rd parameter has a default value of NULL. However, I cannot just write:

dbo.myFunction(Param1, Param2)

I get the error:

An insufficient number of arguments were supplied for the procedure or function dbo.myFunction.

Therefore I've got to write:

dbo.myFunction(Param1, Param2, NULL)
dbo.myFunction(Param1, Param2, default)

Is there a way I can just write dbo.myFunction(Param1, Param2)? I think this is a lot cleaner (and saves me having to modify an existing function which I've added a new param to!)

Alex K. · Accepted Answer · 2011-07-06 13:11:06Z

3

They are default parameters as opposed to optional parameters & must always be passed in the call with a value or default.

If you wanted to you could make your existing dbo.myFunction(Param1, Param2) call dbo.myFunctionEx(Param1, Param2, default)

answered Jul 6, 2011 at 13:11

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Curtis Over a year ago

Is there anyway of having an optional parameter? If a new parameter is added to an existing UDF, whats the standard protocol for the existing calls to that function? Just go through them all and add 'default'?

Alex K. Over a year ago

If you call a function with too few or too many arguments you will get an error so you must either modify the existing calls to include default or create the revised proc with a different name

Curtis Over a year ago

Thanks Alex, do you know what the reason is behind this? Seems to me like theres no reason why default cannot just be 'assumed' if its not entered.

Aaron Bertrand Over a year ago

I don't know if there is a reason other than to keep parsing rules simplified, and that's the way it's always worked. If you really, really, really want an overload to save you a few keystrokes, you could just create a second function that takes the first two parameters and calls the original function, adding the third parameter as NULL. As Alex rightly points out, these are default parameters, not optional parameters.

Curtis Over a year ago

@Aaron It's not so much to save a few keystrokes, as this function has been called about 50 times, and I'm concerned about missing a few out, causing errors all over the application. However, this sounds like a better long term solution than creating a second function. Cheers

Collectives™ on Stack Overflow

SQL Function and default values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related