Examples are provided for using multiple parameters in a procedure.
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.
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, SAP ASE 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, SAP ASE attempts to execute the procedure and then the command. For example, if you issue:
sp_help checkpoint
SAP ASE returns the output from sp_help and runs the checkpoint command. Using delimited identifiers for procedure parameters can produce unintended results.