Menu
Yes I know I can do this with stored procedures. I'm wondering if it's possible with functions.
448
A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call. The default value of a parameter is used when: No value for the parameter is specified in the procedure call.
It is better to use the " = NULL" if you are adding a new optional parameter to an existing stored proc. The reason is, you may not be aware of ALL the code that calls this proc. Hence, unless you make it optional using the " = NULL", for all the places that you may have missed to pass in a value, it will break.
SQL Server provides query hints in which you can force query optimizer to use a particular operator in the execution plan. To use a specific operator, SQL Server might need to change other operators as well.
The 'Optimize for Unknown' feature follows the premise that trying to get a consistent execution time for a given set of parameters and re-using a stable execution plan is better than spending CPU to compile a special, unique flower of an execution plan every time a query runs.
Not like in a stored proc - a parameter still needs to be placed in each argument position, but you can specify that it use the default from the declaration:
SELECT yourschema.yourudf(x, y, z, DEFAULT, a, b, c, DEFAULT)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
