Optional Input Arguments for functions in T-SQL ?

[pradipto]

Hi,

I am a relatively new bee in T-SQL. Suppose I want to write a function that usually takes 2 parameters where the user who uses it has the option of giving say 1 parameter. So I tried to give a default value as following:

-------
Create Function dbo.MyFunction(@Var1 smalldatetime, @Var2 smalldatetime = null)
Returns smalldatetime
As Begin
Declare @Var3 smalldatetime

If @Var2 is null
<do some operation here to calculate @Var3>
Else
<do some other operation here to calculate @Var3>
...
...
Return (@Var3)
-------

Now, this function works fine when I call it as

Select dbo.MyFunction('2004-01-01',null)

but if I try and call it as

Select dbo.MyFunction('2004-01-01')

then it does NOT work and gives me an error:

Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.MyFunction.

How can I make the function take an optional number of arguments?

Thanks

 

[vongrunt]

Unfortunately, optional values in functions don't work that way. You cannot omit them; specify default keyword instead:

select dbo.myFunction( '2004-01-01', default )