Making SQL Server Function Changes Backwards Compatible

By: Aaron Bertrand | Updated: 2020-03-18 | Comments (1) | Related: > TSQL

Problem

A colleague was recently trying to deploy a change to a SQL Server user defined function, where she had removed one of the input parameters. Unfortunately, this function was also called from stored procedures, both in the source database and other databases on the instance, as well as from ad hoc code in the application. This created a chicken-and-egg scenario, since she couldn't change the function first without breaking the application, and she couldn't change the application first without breaking the function calls.

Solution

First, let's make a fictitious function with an input parameter we can feasibly eliminate (because it doesn't make sense):

CREATE FUNCTION dbo.CalculateSalesCommission
(
  @ProductID int, @ShoeSize tinyint
)
RETURNS decimal(5,4)
AS
BEGIN
  RETURN 
 (
    SELECT CASE WHEN @ProductID % 7 = 0 THEN 0.04 ELSE 0.03 END
    + @ShoeSize / 100.0
  );
END
GO
-- sample usage:
SELECT [41,12] = dbo.CalculateSalesCommission(41,12), -- 0.1500
       [42,12] = dbo.CalculateSalesCommission(42,12), -- 0.1600
       [42,14] = dbo.CalculateSalesCommission(42,14); -- 0.1800

Then we can envision multiple stored procedures and ad hoc queries that call the function, for example:

CREATE PROCEDURE dbo.GetCommissions
  @ProductID int
AS
BEGIN
  -- pretend this comes from a table, or context_info, or ...:
  DECLARE @ShoeSize tinyint = 12; 
  SELECT [Commission] = dbo.CalculateSalesCommission(@ProductID,@ShoeSize);
END
GO

Now, back to the change we need to make. Clearly, a sales associate's shoe size should have nothing to do with their sales commission, so we want to remove that calculation. But if we alter the function first, to remove the input parameter and the calculation:

ALTER FUNCTION dbo.CalculateSalesCommission
(
  @ProductID int--, @ShoeSize tinyint
)
RETURNS decimal(5,4)
AS
BEGIN
  RETURN 
 (
    SELECT CASE WHEN @ProductID % 7 = 0 THEN 0.04 ELSE 0.03 END
    --+ @ShoeSize / 100.0
  );
END
GO

The procedures and ad hoc queries, which all still reference both parameters, will start breaking with the following error:

Msg 8144, Level 16, State 2, Procedure dbo.GetCommissions, Line 8
Procedure or function dbo.CalculateSalesCommission has too many arguments specified.

And if we try to change the procedure first, to stop passing the argument:

ALTER PROCEDURE dbo.GetCommissions
  @ProductID int
AS
BEGIN
  --DECLARE @ShoeSize tinyint = 12;
  SELECT [Commission] = dbo.CalculateSalesCommission(@ProductID);--, @ShoeSize);
END
GO

Well, that won't work at all (and deferred name resolution will not help you here):

Msg 313, Level 16, State 2, Procedure GetCommissions, Line 8
An insufficient number of arguments were supplied for the procedure or function dbo.CalculateSalesCommission.

So, yeah, we definitely have a chicken-and-egg scenario here.

The most important thing is that we change the logic to no longer care about shoe size. Whether the argument is still passed into the function is largely irrelevant to the business, if the value passed in is just going to be ignored. You could implement this in multiple steps or just implement one or the other, depending on urgency, the gap between changing the database and the code around it, and your tolerance for meaningless bits of code staying around.

Option 1 - Make the argument a no-op (no operation)

Because you can't immediately change the function to drop the argument completely, without simultaneously changing all of the code that calls it, you could start by making it "sort of" optional, and remove the logic inside the function:

ALTER FUNCTION dbo.CalculateSalesCommission
(
  @ProductID int, @ignore_me tinyint = NULL 
)
RETURNS decimal(5,4)
AS
BEGIN
  RETURN 
 (
    SELECT CASE WHEN @ProductID % 7 = 0 THEN 0.04 ELSE 0.03 END
  );
END
GO

I say "sort of" optional because the value you pass gets ignored, but you still need to pass something. This allows the existing code to continue calling the function, with the shoe size value, as if nothing had changed. You could then slowly change each piece of code one at a time to (a) eliminate any logic involved with determining the shoe size, and (b) passing NULL or DEFAULT keywords to the function instead:

SELECT [Commission] = dbo.CalculateSalesCommission(41, NULL);
-- or
SELECT [Commission] = dbo.CalculateSalesCommission(41, DEFAULT);

You can gradually introduce that change to the procedures and application code over any time frame, so that the code change does not have to be tightly coupled to the function change. You could start this way if it's more important to remove the shoe size information from the callers (again, say, determining the shoe size was an expensive calculation, or violated GDPR, or the data was simply no longer available). Once that is done (or you could skip this step), you could introduce a second function.

Option 2 - Move the logic to a new function

In order to move away from the old function with two arguments (whether they are still passing real shoe size values, or they are now accepting NULL / DEFAULT), you could create a new function with a temporary name. You would create this with only the one argument, put the logic there, and – when it's ready – just make the old function call the new function. This allows some old code to continue calling the old form of the function, but lets you adjust other code to call the new function without the useless argument.

CREATE FUNCTION dbo.CalculateSalesCommissionPartDeux
(
  @ProductID int-- no second argument
)
RETURNS decimal(5,4)
AS
BEGIN
  RETURN 
 (
    SELECT CASE WHEN @ProductID % 7 = 0 THEN 0.04 ELSE 0.03 END
  );
END
GO

Once that's in place, you can change the original function to remove the logic altogether and just call the new function, only passing the first argument:

ALTER FUNCTION dbo.CalculateSalesCommission
(
  @ProductID int, @ShoeSize tinyint = NULL
)
RETURNS decimal(5,4)
AS
BEGIN
  RETURN 
 (
    SELECT dbo.CalculateSalesCommissionPartDeux(@ProductID);
  );
END
GO

In this case, too, existing code can continue calling the old function, but it will be using the new logic (and that logic will still be only in one place).

When you have updated all of the calling code to only call the new function with the single argument, and you are sure the old function is no longer being called, you can drop it. At least for scalar functions, you can monitor sys.dm_exec_function_stats to be sure, but please have a backup somewhere just in case you need to restore it for any reason (I'm not worried; you're using source control, right?). You could then even rename the new function to have the old name, and create a synonym to redirect calls to the temporary name until you can clean all those up, too.

Conclusion

There's no magic here – changing an API can be painful. It may be tempting to leave unused parameters in function interfaces, but I think removing those obsolete things are better overall for a long-term architecture. And there are certainly approaches you can take to make those changes smoother and more gradual, so that you don't have to change your entire infrastructure's codebase in one swoop. It just might take more steps than many of us would like.

Next Steps

Read on for related tips and other resources involving making changes backward compatible:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-03-18

Wednesday, March 18, 2020 - 10:44:50 PM - bonskijr	Back To Top (85144)
This is what we do also for sp signature change but having played around PostgreSQL, I wish Sql Server might also have function overloading (https://www.postgresql.org/docs/8.2/xfunc-overload.html)