In the create procedure statement, you can declare null as the default value for individual parameters:
create procedure procedure_name @param datatype [ = null ] [, @param datatype [ = null ]]...
In this case, if the user does not supply a parameter, Adaptive Server executes the stored procedure without displaying an error message.
The procedure definition can specify an action be taken if the user does not give a parameter, by checking to see that the parameter’s value is null. Here is an example:
create procedure showind3
@table varchar(30) = null as
if @table is null
print "Please give a table name."
else
select table_name = sysobjects.name,
index_name = sysindexes.name,
index_id = indid
from sysindexes, sysobjects
where sysobjects.name = @table
and sysobjects.id = sysindexes.id
If the user does not give a parameter, Adaptive Server prints the message from the procedure on the screen.
For other examples of setting the default to null, examine the source text of system procedures using sp_helptext.
