SQL function value is null

1096320 pts.
Tags:
SQL
SQL Server
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.. CODE:
USE [ORDERENTRY]
GO

/****** Object: UserDefinedFunction [dbo].[_BETA1_fn_GetDesignSequence] Script Date: 07/24/2018 10:26:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[_BETA1_fn_GetDesignSequence](
@ShipToCountry VARCHAR(2),
@CustomerNumber VARCHAR(6)
)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @Return VARCHAR(10)

SELECT @Return = DesignSeq
FROM dbo.ProductTypeDesignSequence

--Old logic with just US and CA countries
--WHERE
--(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
WHERE
(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'

RETURN @Return
END

GO

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: