NULL as the default parameter

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 ]]...

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.