 |
|
SQL Server Tips by Burleson |
Named parameters
All the examples already described have the parameters passed in the
same order as they appear when calling the XP. That is called
passing parameters by location but it is also possible to pass
parameters by name.
One detail about ODS is that it keeps track of parameters by their
ordinal position but it does not mean that named parameters are not
possible. The ordinal position for each parameter is fixed and
instead it can be retrieved from the parameter’s name using
srv_paramnumber. Instead of using hard coded numbers for each
parameter, variables will store the parameter’s ordinal.
Validating the input has a new step, after verifying the number of
parameters entered, the parameter names are used in the ODS function
srv_paramnumber, trying to locate their position. If a parameter
name is not found its position is zero; in this particular code, all
parameters are compulsory and an error message is generated if one
or more are missing. The positions are stored in variables that will
provide access to the parameters.
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 |
