Optional should be OPTIONAL

Yesterday and today, I’ve been working on some changes to our discount system (at work). The majority of the changes dealt with the (mssql) database … stored procedures and user defined functions (ufn). One of the changes was to get the shopping cart subtotal based on different criteria …

• If the shopping cart had an order-level discount in it, and that discount was set up to override any discount exclusions that were set up, the subtotal calculation needed to only exclude those products where the exclusion wasn’t allowed to be overridden.
• If the shopping cart had an order-level discount in it, and that discount was NOT set up to override any discount exclusions that were set up, the subtotal calculation needed to exclude ALL products that had exclusions set up.

We have a ufn set up to calculate the order subtotal. This ufn is already taking into account the discount exclusions. But with my changes, I needed it to now look at the exclusion overrides. I was hoping that ufn’s allowed for optional parameters like php functions do. So I set up a little test to see …

CREATE FUNCTION [dbo].[ufn_MyTest](@Param1 int, @Param2 int = NULL)
RETURNS varchar(500)
AS
BEGIN
DECLARE @MyVariable varchar(500)

IF @Param1 > 10
SET @MyVariable = 'Incoming parameter #1 is greater than 10.'
ELSE
SET @MyVariable = 'Incoming parameter #1 is less than 10.'

IF @Param2 IS NOT NULL
BEGIN
IF @Param2 > 10
SET @MyVariable = @MyVariable + ' Incoming parameter #2 is greater than 10.'
ELSE
SET @MyVariable = @MyVariable + ' Incoming parameter #2 is less than 10.'
END

RETURN @MyVariable
END

The database let me create the function, so I was hopeful that this was gonna work. So I tried executing my little ufn …
SELECT dbo.ufn_MyTest(8)

But that didn’t work. It gave me an error …

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

What? Come on. That second parameter is set up to be optional. So I did some searching and found this tidbit …

from the MSSQL manual
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. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value.

Grrr!! :banghead: So I tried this …

SELECT dbo.ufn_MyTest(8, DEFAULT)

THAT worked. But doesn’t that kind of defeat the purpose of having an “optional” parameter. When I think “optional”, I see that as meaning that the parameter doesn’t have to be passed or it can be, depending on the situation. If I have to put ‘DEFAULT’ in it’s place, that doesn’t make it optional. I could just as well pass in the “optional” value.

To fix my little conundrum, I created a separate ufn and called the appropriate one based on the parameter that I had wanted to pass in as an optional one. That worked like a charm. I just wish that Microsoft would have set up mssql ufn’s to accept truly optional parameters. But I guess I’m not surprised. It is Microsoft after all. :nahnah: