Default Parameters

You can assign a default value for a parameter in the create procedure statement. This value, which can be any constant, is used as the argument to the procedure if the user does not supply one.

Here is a procedure that displays the names of all the authors who have written a book published by the publisher given as a parameter. If no publisher name is supplied, the procedure shows the authors published by Algodata Infosystems.

create proc pub_info 
  @pubname varchar(40) = "Algodata Infosystems" as 
select au_lname, au_fname, pub_name 
from authors a, publishers p, titles t, titleauthor ta 
where @pubname = p.pub_name 
and a.au_id = ta.au_id 
and t.title_id = ta.title_id 
and t.pub_id = p.pub_id

If the default value is a character string that contains embedded blanks or punctuation, it must be enclosed in single or double quotes.

When you execute pub_info, you can give any publisher’s name as the parameter value. If you do not supply any parameter, SAP ASE uses the default, Algodata Infosystems.

exec pub_info

au_lname        au_fname      pub_name                                  
--------------  ------------  --------------------
Green           Marjorie      Algodata Infosystems
Bennet           Abraham       Algodata Infosystems
O’Leary         Michael       Algodata Infosystems
MacFeather      Stearns       Algodata Infosystems
Straight        Dick          Algodata Infosystems
Carson           Cheryl        Algodata   Infosystems
Dull            Ann           Algodata Infosystems
Hunter          Sheryl        Algodata Infosystems
Locksley        Chastity      Algodata Infosystems
 
(9 rows affected, return status = 0)

This procedure, showind2, assigns “titles” as the default value for the @table parameter:

create proc showind2 
@table varchar(30) = titles as 
select table_name = sysobjects.name, 
    index_name = sysindexes.name, index_id = indid 
from sysindexes, sysobjects 
where sysobjects.name = @table 
and sysobjects.id = sysindexes.id

The column headings, for example, table_name, clarify the result display. Here is what showind2 shows for the authors table:

showind2 authors

table_name  index_name        index_id 
----------- -------------   --------- 
authors     auidind                  1 
authors     aunmind                 2 
 
(2 rows affected, return status = 0)

If the user does not supply a value, SAP ASE uses the default, titles.

showind2

table_name  index_name  index_id 
----------- ----------- ---------
titles      titleidind         1 
titles      titleind           2 
 
(2 rows affected, return status =0)

If a parameter is expected but none is supplied, and a default value is not supplied in the create procedure statement, SAP ASE displays an error message listing the parameters expected by the procedure.