Using Multiple Parameters

Examples are provided for using multiple parameters in a procedure.

An example of 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.

Note: 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, 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.