Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - DEFAULT - Providing Default Values to Function Parameters

By: FYIcenter.com

(Continued from previous topic...)

How To Provide Default Values to Function 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 define a default value to a parameter when creating the function, you should use this format: "@parameter_name data_type = default_value".
  • To use the default value of a parameter when executing the function, you should use the keyword DEFAULT as the input value for that parameter.

The tutorial exercise below shows you how provide default values to function parameters:

USE FyiCenterData;
GO

CREATE FUNCTION Age_In_Days (
   @birth_date DATETIME,
   @today DATETIME = NULL
   )
   RETURNS INT
AS BEGIN
   IF @today IS NULL SET @today = GETDATE();
   RETURN DATEDIFF(DAY, @birth_date, @today);
END;
GO

-- Default value is used
PRINT 'Age in days: '+STR(
   dbo.Age_In_Days('01-Jan-2007', DEFAULT));
GO
Age in days:        138

-- Default value is not used
PRINT 'Age in days: '+STR(
   dbo.Age_In_Days('01-Jan-2007', '11-May-2007'));
GO
Age in days:        130

-- Can not skip input values 
-- even for parameters with default values
PRINT 'Age in days: '+STR(
   dbo.Age_In_Days('01-Jan-2007'));
GO
Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied 
for the procedure or function dbo.Age_In_Days.

(Continued on next topic...)

  1. What Are User Defined Functions?
  2. What Are the Differences between User Defined Functions and Stored Procedures?
  3. How To Create a Simple User Defined Function?
  4. How To Use User Defined Functions in Expressions?
  5. How To List All User Defined Functions in the Current Database?
  6. How To Drop an Existing User Defined Function?
  7. How To Generate CREATE FUNCTION Script on an Existing Function?
  8. How To Get the Definition of a User Defined Function Back?
  9. How To Modify an Existing User Defined Function?
  10. How To Create User Defined Functions with Parameters?
  11. How To Provide Values to User Defined Function Parameters?
  12. Can You Pass Expressions to Function Parameters?
  13. How To Provide Default Values to Function Parameters?
  14. How Many Categories of Functions based Their Return Modes?
  15. How Many Ways to Create Table-Valued Functions?
  16. How To Create an Inline Table-Valued Function?
  17. How To Create an Multi-Statement Table-Valued Function?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...