How to call a user defined function with a default parameter

Today it happened one of my team member called up me and ask me how to call his function where he has used default parameter. The snapshot of his function is like this only..

create function fnTest(@param1 int, @param2 int = 1 )

returns int

as

begin      

      return @param2      

end

His question was how to call this function in select statement, I simple said, u can either pass two parameter while calling this function, if you are doing so then the second default parameter @param2 value  will get replaced by the new one you pass while calling this function.

select dbo.fnTest(2,5)

-----------

5

(1 row(s) affected)

Where as  if you pass only one parameter to the calling funtion then the @param2 will take the dufault value as 1 in this case.

I give him the below mention line for calling the funtion with one parameter as soon as we excute this line of code we got  error message

select dbo.fnTest(7)

Msg 313, Level 16, State 2, Line 1

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

Belive me I was not aware of this then somebody from other team sitting behind us told to use default keyword while calling funtion with default parameter.

select dbo.fnTest(1,default)

-----------

1

(1 row(s) affected)

Thanks Shekhar for the solution.

Post Reference: Vikram Aristocratic Elfin Share