How To Provide Default Values to Stored Procedure Parameters in SQL Server Transact-SQL?

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:

USE FyiCenterData;
GO

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
Procedure or Function 'diff_in_days' expects 
parameter '@start_date', which was not supplied.

⇒ OUTPUT - Defining Output Parameters in Stored Procedures in SQL Server

⇐ Passing Expressions to Stored Procedure Parameters in SQL Server

⇑ Using Stored Procedures in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials