Default values to SQL function parameters

Unlike stored procedure parameters, When a parameter of the function has a default value, the keyword 'DEFAULT' must be specified when calling the function in order to get the default value. 

CREATE FUNCTION [dbo].[FUNCTIONNAME] (

@P1 int, 

@P2 int =0 

)........

select * from dbo.FUNCTIONNAME(1)

Result: an error message:

An insufficient number of arguments were supplied for the procedure or function dbo.FUNCTIONNAME.

Solution: 

We have to use the 'DEFAULT' keyword:

Select * from dbo.FUNCTIONNAME(1, DEFAULT)

If the 2 parameters have default, 

CREATE FUNCTION [dbo].[FUNCTIONNAME] (

@P1 int =0, 

@P2 int =0 

)........

we have to use 'DEFAULT' twice:

Select * from dbo.FUNCTIONNAME(DEFAULT, DEFAULT)