If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter.

To provide a default value to a parameter, you should use this format: "@parameter_name data_type = default_value". The tutorial exercise below shows you how provide default values to stored procedure parameters:

DROP PROCEDURE diff_in_days;

GO

CREATE PROCEDURE diff_in_days

@start_date DATETIME,

@end_date DATETIME = '19-May-2007'

AS BEGIN

PRINT CONVERT(VARCHAR(20),@end_date,107)

+ ' - '

+ CONVERT(VARCHAR(20),@start_date,107)

+ ' = '

+ STR(DATEDIFF(DAY, @start_date, @end_date));

END;

GO

-- Default value is used

EXEC diff_in_days

@start_date='01-Jan-2007';

GO

May 19, 2007 - Jan 01, 2007 = 138

-- Default value is not used

EXEC diff_in_days

@start_date='01-Jan-2007',

@end_date='11-May-2007';

GO

May 11, 2007 - Jan 01, 2007 = 130

-- Input value must be supplied for a parameter

-- without a default value

EXEC diff_in_days

@end_date='11-May-2007';

GO

Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0