As in any programming language, we can even set the default parameters for the Functions and Procedures on SQL Server. But calling these functions or procedures will differ with how we call in any programming languages.

Let’s look at the following example table-valued function, which takes one parameter as input and another parameter as default parameter.

CREATE FUNCTION fntestdefaultparam
(	
  @Param1 INT,
  @Param2 INT = 2
)
RETURNS @abc TABLE (result int )
AS
BEGIN
  insert into @abc values(@Param1 + @Param2)

  return;
END;
GO

Now we can call this function as follows.

select * from fntestdefaultparam(1,default)

as observed, if you want to keep the default value we need to pass a default keyword in a function call where as in normal programming we won’t send any keywords.