Here is a variant of au_info that uses defaults with wildcard characters for both parameters:
create proc au_info2 @lastname varchar(30) = "D%", @firstname varchar(18) = "%" as select au_lname, au_fname, title, pub_name from authors, titles, publishers, titleauthor where au_fname like @firstname and au_lname like @lastname and authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id and titles.pub_id = publishers.pub_id
If you execute au_info2 with no parameters, all the authors with last names beginning with “D” are returned:
au_info2
au_lname au_fname title pub_name -------- ------- ------------------------- ------------- Dull Ann Secrets of Silicon Valley Algodata Infosystems DeFrance Michel The Gourmet Microwave Binnet & Hardley (2 rows affected)
If defaults are available for parameters, parameters can be omitted at execution, beginning with the last parameter. You cannot skip a parameter unless NULL is its supplied default.
If you supply parameters
in the form @parameter = value,
you can supply parameters in any order. You can also omit a parameter
for which a default has been supplied. If you supply one value in
the form @parameter = value,
then supply all subsequent parameters this way.
As an example of omitting the second parameter when defaults for two parameters have been defined, you can find the books and publishers for all authors with the last name “Ringer” like this:
au_info2 Ringer
au_lname au_fname title Pub_name -------- -------- --------------------- ------------ Ringer Anne The Gourmet Microwave Binnet & Hardley Ringer Anne Is Anger the Enemy? New Age Books Ringer Albert Is Anger the Enemy? New Age Books Ringer Albert Life Without Fear New Age Books
If a user executes a stored procedure and specifies more parameters than the number of parameters expected by the procedure, Adaptive Server ignores the extra parameters. For example, sp_helplog displays the following for the pubs2 database:
sp_helplog
In database ‘pubs2’, the log starts on device ‘pubs2dat’.
If you erroneously add some meaningless parameters, the output of sp_helplog is the same:
sp_helplog one, two, three
In database ‘pubs2’, the log starts on device ‘pubs2dat’.
Remember that SQL is a free-form language. There are no rules about the number of words you can put on a line or where you must break a line. If you issue a stored procedure followed by a command, Adaptive Server attempts to execute the procedure and then the command. For example, if you issue:
sp_help checkpoint
Adaptive Server returns the output from sp_help and runs the checkpoint command. Using delimited identifiers for procedure parameters can produce unintended results.
