|
 |
|
SQL Server Tips by Burleson |
Default values
A parameter from an SP might have a value that is constant for most
situations and it would be simpler to make it optional and with a
default value. This way, the XP call would have it included in its
parameters, the few times its value would be different from the
default value. If the parameter is passed by location, the last one
can be omitted, if it is optional, meaning that it will take the
default value. The keyword DEFAULT, if used in the position of a
certain parameter, causes that parameter to take its default value.
If no default value is defined an error message is generated. If the
parameter is passed by name, the missing name could mean that the
parameter should take the default value.
This example is an XP that takes two parameters, the second one
taking a default value when omitted. The output is the concatenation
of both input values, separated by a space character, and all
lowercase, with the exception of the first characters from both
input parameters. When validating the input, the number of
parameters will be in a range, in this example it is only from one
to two but it could be more, depending on the number of default
parameters. A Boolean variable can store whether a parameter will
have its default value assigned or not. This variable will determine
if the parameter’s data is to be retrieved or ignored and, before
processing the data, it will serve as a flag to ensure that the
default value is stored in the corresponding temporary variable.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |