You can assign a default value for the 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
Note that 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, Adaptive Server 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, Adaptive Server 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, Adaptive Server displays an error message listing the parameters expected by the procedure.
