We needed a change recently as more shipments going international. I needed to make changes to our function to return the new motor prefixes. The old worked fine as if it was not US or CA we used XX to show international. We had just the three country values. Now each country has it's own code and the table is about 169 in size. The problem I have is in the modified function to handle it but there are times it returns a null value. Maybe a typo in the entry process. They country code passed via a massive stored procedure may not have a hit in the table. Is there a way to set a default return value if the value is null? here is the function code. I know I may be asking the impossible..
/****** Object: UserDefinedFunction [dbo].[_BETA1_fn_GetDesignSequence] Script Date: 07/24/2018 10:26:45 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[_BETA1_fn_GetDesignSequence](
RETURNS VARCHAR(10) AS
DECLARE @Return VARCHAR(10)
SELECT @Return = DesignSeq
--Old logic with just US and CA countries
--(CASE WHEN @ShipToCountry = 'CA' or @ShipToCountry = 'US' THEN @ShipToCountry ELSE 'XX' END = Country ) AND
--(CASE WHEN @CustomerNumber = '777777' THEN 'TST' ELSE 'PRD' END = MFGLabel)
--AND ActiveSeq = 'X'
--NEW logic for all INTERNATIONAL countries
(CASE WHEN @ShipToCountry is not null THEN @ShipToCountry ELSE 'US' END = Country ) AND
(CASE WHEN @CustomerNumber = '777777' THEN 'TST' ELSE 'PRD' END = MFGLabel)
AND ActiveSeq = 'X'