SQL Function Returning Truncated Text

Question

I am trying to clean up some fields in a database. I created a stored procedure and a couple functions. The output from the stored procedure is truncating the text of one of the fields.

Here is the store procedure:

CREATE PROCEDURE [dbo].[SalesforceAccount_Maint]
AS
BEGIN
    -- Checks for and Deletes Tables if Exists
    IF OBJECT_ID(N'tempdb..#SalesforceAccount_Changes', N'U') IS NOT NULL
        DROP TABLE #SalesforceAccount_Changes

    -- Copies Data into Temp Tables for Processing
    SELECT * INTO #SalesforceAccount_Changes FROM SalesforceAccount

    -- Converts State to 2 Character Abbreviation
    UPDATE #SalesforceAccount_Changes 
       SET [BillingState] = dbo.fn_convert_state( 2, [BillingState], [BillingCountry] ) 
       WHERE [BillingCountry] = 'United States' 
         AND LEN( [BillingState] ) > 2 
         AND dbo.fn_convert_state( 2, [BillingState], [BillingCountry]) IS NOT NULL 
         AND [BillingState] IS NOT NULL
    UPDATE #SalesforceAccount_Changes 
       SET [ShippingState] = dbo.fn_convert_state(2, [ShippingState], [ShippingCountry])    
     WHERE [ShippingCountry] = 'United States' 
       AND LEN( [ShippingState] )   > 2 
       AND dbo.fn_convert_state(2, [ShippingState], [ShippingCountry]) IS NOT NULL 
       AND [ShippingState] IS NOT NULL

    -- Sets Region
    UPDATE #SalesforceAccount_Changes 
       SET [Sales_Territory__c] = dbo.fn_setRegion( [BillingState] ) 
     WHERE [BillingCountry] = 'United States' 
       AND [BillingState] IS NOT NULL


    SELECT
        a2.id,
        a2.[BillingState],
        a2.[ShippingState],
        a2.[Sales_Territory__c]
      FROM SalesforceAccount a1
        LEFT JOIN #SalesforceAccount_Changes a2
          ON a1.id = a2.id
      WHERE
        a1.[BillingState] != a2.[BillingState] OR
        a1.[ShippingState] != a2.[ShippingState] OR
        a1.[Sales_Territory__c] != a2.[Sales_Territory__c]
END

The issue is with the Region. Here is the setRegion function:

CREATE FUNCTION [dbo].[fn_setRegion] ( @state varchar(255) )
    RETURNS nvarchar(25)

BEGIN

    IF @state IS NULL
        RETURN NULL


    /****    North West (6 States)    ****/
    IF (@state = 'AK' OR @state = 'Alaska')
        OR (@state = 'ID' OR @state = 'Idaho')
        OR (@state = 'MT' OR @state = 'Montana')
        OR (@state = 'OR' OR @state = 'Oregon')
        OR (@state = 'WA' OR @state = 'Washington')
        OR (@state = 'WY' OR @state = 'Wyoming')
    RETURN 'North West'


    /****    South West (7 States)    ****/
    IF (@state = 'AZ' OR @state = 'Arizona')
        OR (@state = 'CA' OR @state = 'California')
        OR (@state = 'CO' OR @state = 'Colorado')
        OR (@state = 'HI' OR @state = 'Hawaii')
        OR (@state = 'NM' OR @state = 'New Mexico')
        OR (@state = 'NV' OR @state = 'Nevada')
        OR (@state = 'UT' OR @state = 'Utah')
    RETURN 'South West'


    /****    North Central (8 States)    ****/
    IF (@state = 'IA' OR @state = 'Iowa')
        OR (@state = 'IL' OR @state = 'Illinois')
        OR (@state = 'MN' OR @state = 'Minnesota')
        OR (@state = 'MO' OR @state = 'Missouri')
        OR (@state = 'ND' OR @state = 'North Dakota')
        OR (@state = 'NE' OR @state = 'Nebraska')
        OR (@state = 'SD' OR @state = 'South Dakota')
        OR (@state = 'WI' OR @state = 'Wisconsin')
    RETURN 'North Central'


    /****    South Central (6 States)    ****/
    IF (@state = 'AR' OR @state = 'Arkansas')
        OR (@state = 'KS' OR @state = 'Kansas')
        OR (@state = 'LA' OR @state = 'Louisiana')
        OR (@state = 'MS' OR @state = 'Mississippi')
        OR (@state = 'OK' OR @state = 'Oklahoma')
        OR (@state = 'TX' OR @state = 'Texas')
    RETURN 'South Central'


    /****    North East (11 States)    ****/
    IF (@state = 'CT' OR @state = 'Connecticut')
        OR (@state = 'DE' OR @state = 'Delaware')
        OR (@state = 'ME' OR @state = 'Maine')
        OR (@state = 'MD' OR @state = 'Maryland')
        OR (@state = 'MA' OR @state = 'New Massachusetts')
        OR (@state = 'NH' OR @state = 'New Hampshire')
        OR (@state = 'NJ' OR @state = 'New Jersey')
        OR (@state = 'NY' OR @state = 'New York')
        OR (@state = 'PA' OR @state = 'Pennsylvania')
        OR (@state = 'RI' OR @state = 'Rhode Island')
        OR (@state = 'VT' OR @state = 'Vermont')
    RETURN 'North East'


    /****    East (6 States)    ****/
    IF (@state = 'IN' OR @state = 'Indiana')
        OR (@state = 'KY' OR @state = 'Kentucky')
        OR (@state = 'MI' OR @state = 'Michigan')
        OR (@state = 'OH' OR @state = 'Ohio')
        OR (@state = 'VA' OR @state = 'Virginia')
        OR (@state = 'WV' OR @state = 'West Virginia')
    RETURN 'East'


    /****    South East (6 States)    ****/
    IF (@state = 'AL' OR @state = 'Alabama')
        OR (@state = 'FL' OR @state = 'Florida')
        OR (@state = 'GA' OR @state = 'Georgia')
        OR (@state = 'NC' OR @state = 'North Carolina')
        OR (@state = 'SC' OR @state = 'South Carolina')
        OR (@state = 'TN' OR @state = 'Tennessee')
    RETURN 'South East'

    RETURN NULL 
END

My Output looks like this:

id  BillingState    ShippingState   Sales_Territory__c
0011T00002IPa2EQAT  GA  NULL    South East
0011T00002IPVttQAH  NC  NULL    South East
0011T00002IPYZwQAP  OK  NULL    Sout
0011T00002K0JE1QAN  CA  NULL    Sout
0011T00002K3swOQAR  OH  NULL    East
00130000001epccAAA  CA  NULL    Sout
00130000001epciAAA  CA  NULL    Sout
00130000001epcjAAA  CA  NULL    Sout
00130000001epcnAAA  CA  NULL    Sout
00130000001epcqAAA  CA  NULL    Sout
00130000001epcyAAA  CA  NULL    Sout
00130000001epd4AAA  CA  NULL    Sout
00130000001epd9AAA  CA  NULL    Sout
00130000001epdiAAA  CA  NULL    Sout
00130000001epdjAAA  CA  NULL    Sout
00130000001epdnAAA  CA  NULL    Sout
00130000001epe2AAA  CA  NULL    Sout
00130000001epe5AAA  CA  NULL    Sout
00130000001epeBAAQ  CA  NULL    Sout
00130000001epeFAAQ  VA  NULL    East
00130000001epehAAA  FL  PA  South East

Sales_Territory__c is nvarchar(255) in the SalesforceAccount table. However, you can see that it isn't truncating everything, so I don't believe it's a field size issue. 'South East' is the only one that isn't being truncated.

I'm at a loss here, any help is appreciated!

Answer 1

How about defining a variable to be returned and assign the value to that.

CREATE FUNCTION [dbo].[fn_setRegion] ( @state varchar(255) )
    RETURNS nvarchar(25)

BEGIN

    Declare @Region nvarchar(255) = NULL


    IF @state IS NULL
        RETURN @Region


    /****    North West (6 States)    ****/
    IF (@state = 'AK' OR @state = 'Alaska')
        OR (@state = 'ID' OR @state = 'Idaho')
        OR (@state = 'MT' OR @state = 'Montana')
        OR (@state = 'OR' OR @state = 'Oregon')
        OR (@state = 'WA' OR @state = 'Washington')
        OR (@state = 'WY' OR @state = 'Wyoming')

        Set @state = 'North West'



    /****    South West (7 States)    ****/
    Else IF (@state = 'AZ' OR @state = 'Arizona')
        OR (@state = 'CA' OR @state = 'California')
        OR (@state = 'CO' OR @state = 'Colorado')
        OR (@state = 'HI' OR @state = 'Hawaii')
        OR (@state = 'NM' OR @state = 'New Mexico')
        OR (@state = 'NV' OR @state = 'Nevada')
        OR (@state = 'UT' OR @state = 'Utah')

        Set @state = 'South West'

    ...
    ...


    RETURN @Region 

or instead of this function, keep all data in a table, and query the region from table

Answer 2

My best guess is that this code is not doing what you expect:

UPDATE #SalesforceAccount_Changes
    SET [Sales_Territory__c] = dbo.fn_setRegion( [BillingState] )
    WHERE [BillingCountry] = 'United States' AND [BillingState] IS NOT NULL;

That would be because BillingCountry does not match. Based on your result set BillingState looks correct.