Function parameter's defaults

There's a lot to be said about differences between functions/procedures in T-SQL and PL/SQL. This time we're gonna focus on function parameters and, surprise, how bad T-SQL is in this regard. Specifically when using default values.

Let's start with a simple, pointless function:

create or alter function [dbo].[StupidFunction] ()
returns float
as begin
 return 1.0;
end
go

It does what it's intended to do, returns 1:

select [dbo].[StupidFunction] ()

----------------------
1

I want to talk about parameters but this function has none. Let's add some. Let's say this function will be multiplying two numbers that are provided as its parameters:

create or alter function [dbo].[StupidFunction] (
 @num1 float,
 @num2 float
)
returns float
as begin
 return @num1 * @num2;
end
go

There's no way it won't work:

select [dbo].[StupidFunction] ( 2, 3 ) as rvStupidFunction

rvStupidFunction
----------------------
6

And just for a good measure, let's try executing this in T-SQL:

declare @rv float
set @rv = [dbo].[StupidFunction] ( 2, 3 );
print @rv

and again, no problems there, we get a simple 6 in the output.

The same in the Oracle world would be:

create or replace function StupidFunction ( num1 in number, num2 in number ) 
return number
is
begin
    return num1 * num2;
end;
/

select StupidFunction ( 2, 3 ) as rvStupidFunction from dual;

begin
    dbms_output.put_line ( StupidFunction ( 2, 3 ) );
end;
/

Let's say that we've used this function in multiple places but now we want it to make more but, for some reason, we don't want to create new function and duplicate it's logic, we want to add new parameter here. The parameter will tell the function if it should multiply or exponentiate:

create or alter function [dbo].[StupidFunction] (
 @num1 float,
 @num2 float,
 @expn bit
)
returns float
as begin
 declare @rv float
 if @expn=0 
  set @rv = @num1 * @num2;
 else 
  set @rv = power(@num1, @num2);

 return @rv;
end;
go

Ok, let's see how it behaves now:

select [dbo].[StupidFunction] ( 2, 3, 0 ) as rvStupidFunction
select [dbo].[StupidFunction] ( 2, 3, 1 ) as rvStupidFunction

declare @rv float
set @rv = [dbo].[StupidFunction] ( 2, 3, 0 );
print @rv
set @rv = [dbo].[StupidFunction] ( 2, 3, 1 );
print @rv

rvStupidFunction
----------------------
6

(1 row affected)

rvStupidFunction
----------------------
8

(1 row affected)

6
8

Cool, looks great, isn't it?
Let's see how our old code works, shall we?

select [dbo].[StupidFunction] ( 2, 3 ) as rvStupidFunction

declare @rv float
set @rv = [dbo].[StupidFunction] ( 2, 3 );
print @rv

Msg 313, Level 16, State 2, Line 24
An insufficient number of arguments were supplied for the procedure or function dbo.StupidFunction.

Wait... What? Hmmmm.....
Ok,  that makes sense, we now have additional parameter that we haven't used here but I don't want to make ANY amends to already existing code. What should I do?
Yup - I should have defaulted the new parameter to some value. The only sane option is to make it work as before so the default should be 0 (false). Let's see how it behaves now:

create or alter function [dbo].[StupidFunction] (
 @num1 float,
 @num2 float,
 @expn bit = 0
)
returns float
as begin
 declare @rv float
 if @expn=0 
  set @rv = @num1 * @num2;
 else 
  set @rv = power(@num1, @num2);

 return @rv;
end;
go

select [dbo].[StupidFunction] ( 2, 3 ) as rvStupidFunction

Msg 313, Level 16, State 2, Line 24
An insufficient number of arguments were supplied for the procedure or function dbo.StupidFunction.

What? Again?

Let's look in the docs...
Oh, I should have used a special keyword now...

select [dbo].[StupidFunction] ( 2, 3, default ) as rvStupidFunction

declare @rv float
set @rv = [dbo].[StupidFunction] ( 2, 3, default );
print @rv

Now it works:
rvStupidFunction
----------------------
6

(1 row affected)

6


B u t   w h a t ' s   t h e   p o i n t ?

Why we want to use defaults anyway? What's their inteded use? First of all we use it for new parameters, when we do have some working code and we need to extend it with some functionality and save us from the cost and risk of amending too much of existing code, we want to make transparent changes. In T-SQL it's virtually impossible so WHAT'S THE POINT? Why does the SQL Server even mentions this crippled functionality?

There's just one (and only), very cumbersome way to make it work in the sane way which is by using the EXECUTE statement:

declare @rv float
declare @v1 float = 2
declare @v2 float = 3
EXECUTE @rv = [dbo].[StupidFunction] @v1, @v2
print @rv
declare @exp bit = 1
EXECUTE @rv = [dbo].[StupidFunction] @v1, @v2, @exp
print @rv

but then you must:
- design everything to use this syntax right from the beginning
- you can't use this function in plain SQL
- you're getting yourself into other T-SQL problem with parameters which is a topic for another post

How does it work in Oracle? Easy:

create or replace function StupidFunction ( num1 in number, num2 in number, expn in number := 0 ) 
return number
is
    rv number;
begin
    if expn = 0 then
        rv := num1 * num2;
    else
        rv := power(num1,num2);
    end if;
    return rv;
end;
/

select StupidFunction ( 2, 3 ) as rvStupidFunction from dual;
select StupidFunction ( 2, 3, expn=>1 ) as rvStupidFunction from dual;

begin
    dbms_output.put_line ( StupidFunction ( 2, 3 ) );
    dbms_output.put_line ( StupidFunction ( 2, 3, expn=>1 ) );
end;
/

Function STUPIDFUNCTION compiled

RVSTUPIDFUNCTION
----------------
               6

RVSTUPIDFUNCTION
----------------
               8

6
8
PL/SQL procedure successfully completed.


That's how we roll...
Best