Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Providing Default Values to Procedure Parameters

By: FYIcenter.com

(Continued from previous topic...)

How To Provide Default Values to Stored Procedure Parameters?

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.

(Continued on next topic...)

1. What Are Stored Procedures?
2. How To Create a Simple Stored Procedure?
3. How To Execute a Stored Procedure?
4. How To List All Stored Procedures in the Current Database?
5. How To Drop an Existing Stored Procedure?
6. How To Create a Stored Procedure with a Statement Block?
7. How To End a Stored Procedure Properly?
8. How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?
9. How To Get the Definition of a Stored Procedure Back?
10. How To Modify an Existing Stored Procedure?
11. How To Create Stored Procedures with Parameters?
12. How To Provide Values to Stored Procedure Parameters?
13. What Are the Advantages of Passing Name-Value Pairs as Parameters?
14. Can You Pass Expressions to Stored Procedure Parameters?
15. How To Provide Default Values to Stored Procedure Parameters?
16. How To Define Output Parameters in Stored Procedures?
17. How To Receive Output Values from Stored Procedures?
18. How To Create a Local Temporary Stored Procedure?
19. Can Another User Execute Your Local Temporary Stored Procedures?