MS SQL Server Concepts and Programming Question:

Download Questions PDF

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