New optional parameter in SQL Server database function

Question

I have a stored procedure that looks like this:

CREATE FUNCTION [my_schema].[isEligible]
    (@product NUMERIC(8))
RETURNS INT
AS
BEGIN
    DECLARE @result INT;
    /* do eligibility checking, setting @result */
    RETURN @result;
END;

This is referenced in several existing queries similar to this:

SELECT *
FROM person
WHERE my_schema.isEligible(?) > -1 -- negative numbers are eligibility error codes

Where ? is the parameter for a given product id.

In one of the several queries that call the function, I need to pass in a new additional parameter, @exception. I want to have the function behave by default like it did before, and only change behavior for the one call that will be passing in a value for @exception, so I have added the new parameter like so:

CREATE FUNCTION [my_schema].[isEligible]
    (@product NUMERIC(8),
     @exception CHAR(1) = 'N')
RETURNS INT 
AS
BEGIN
    DECLARE @result INT;
    /*
    do eligibility checking (conditionally accounting for
    @exception being 'Y'), and setting @result 
    */
    RETURN @result;
END;

But when I deploy the new version of the function and run a query which only passes in @product I'm getting An insufficient number of arguments were supplied for the procedure or function [my_schema].[isEligible]. I thought that adding = 'N' would provide a default value and avoid this issue.

I read here that sometimes this generic sounding error is actually hiding other errors which aren't allowed to bubble up, but I don't know if that's the case, since it's talking about procedures and this is a function. Not sure if there's a difference or if functions don't support optional arguments like procedures.

I'd love to not have to pass in NULL for the new parameter if possible, which would simplify the rollout of the updated version of the function. Is there a way to do call this function both like isEligible(?) and isEligible(?, ?) or do I need the first one to be isEligible(?, NULL) for it to work?

In case anyone is wondering why a function instead of a subquery, the function calls other database functions and has a fair amount of logic. That plus making it DRY where it's used in multiple queries I assume is why it was originally coded this way. — jinglesthula, Jun 13 '18 at 17:45
There's a good idea in the accepted answer to the duplicate: Create your 2-param function under a new name and turn your old function into a wrapper that calls the new function with default for the new param. Then you don't have to change any existing function calls, and only have to remember to use the new name when you want to use both params. — Tab Alleman, Jun 13 '18 at 17:49

score 1 · Answer 1 · answered Jun 13 '18 at 17:44

1

You could call your function as:

SELECT *
FROM person
WHERE my_schema.isEligible(?, default) > -1

and:

SELECT *
FROM person
WHERE my_schema.isEligible(?, value) > -1

answered Jun 13 '18 at 17:44

Lukasz Szozda

162,964
23
234
275

Ah, right. The `NULL` I was guessing at would actually make `@exception` be `NULL` rather than triggering the default of `'N'`. So using `default` in the calling code would be necessary though, rather than not having anything passed at all allowing the default value to kick in automatically? – jinglesthula Jun 13 '18 at 17:47
@jinglesthula Yes, it is required. – Lukasz Szozda Jun 13 '18 at 17:59

New optional parameter in SQL Server database function

1 Answers1