title: New optional parameter in SQL Server database function
body: <p>I have a stored procedure that looks like this:</p>
  
  <pre><code>CREATE FUNCTION [my_schema].[isEligible]
      (@product NUMERIC(8))
  RETURNS INT
  AS
  BEGIN
      DECLARE @result INT;
      /* do eligibility checking, setting @result */
      RETURN @result;
  END;
  </code></pre>
  
  <p>This is referenced in several existing queries similar to this:</p>
  
  <pre><code>SELECT *
  FROM person
  WHERE my_schema.isEligible(?) &gt; -1 -- negative numbers are eligibility error codes
  </code></pre>
  
  <p>Where <code>?</code> is the parameter for a given product id.</p>
  
  <p>In one of the several queries that call the function, I need to pass in a new additional parameter, <code>@exception</code>. 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 <code>@exception</code>, so I have added the new parameter like so:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>But when I deploy the new version of the function and run a query which only passes in <code>@product</code> I'm getting <code>An insufficient number of arguments were supplied for the procedure or function [my_schema].[isEligible]</code>. I thought that adding <code>= 'N'</code> would provide a default value and avoid this issue.</p>
  
  <p>I read <a href="https://siderite.blogspot.com/2015/08/change-data-capture-yes-there-are.html" rel="nofollow noreferrer">here</a> 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.</p>
  
  <p>I'd love to not have to pass in <code>NULL</code> 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 <code>isEligible(?)</code> and <code>isEligible(?, ?)</code> or do I need the first one to be <code>isEligible(?, NULL)</code> for it to work? </p>