Case 表达式只能嵌套到 10 级 [英] Case expressions may only be nested to level 10

问题描述

我有以下代码来处理电话号码，例如澳大利亚的国家代码+61、61、001161 等.问题是我不能再插入任何 CASE 语句:CASE WHEN LEFT(@BPartyNo, 4) = '+610'

I have the following code for handling phone numbers such as country code for Australia +61, 61, 001161 etc. The problem that I have I can't insert any CASE statement anymore under: CASE WHEN LEFT(@BPartyNo, 4) = '+610'

它说Case表达式只能嵌套到10级

如何简化此 TSQL 以便我可以放置更多 CASE?

How do I streamline this TSQL so I can put more CASE?

USE [TelcoStage_PROD]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_stg_ProperBPartyNoExtra]    Script Date: 07/12/2010 15:27:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--=====================================================================================================================
-- OBJECT NAME          : dbo.ufn_stg_ProperBPartyNoExtra
-- INPUTS               : @BPartyNo 
-- OUTPUTS              : VARCHAR(32)
-- RETURN CODES         : N/A
-- DEPENDENCIES         : N/A
-- DESCRIPTION          : This function is used to get the extra after 10 character (MNET or S)
--
-- EXAMPLES (optional)  : N/A
--
-- HISTORY:
-- #-----------------------------------------------------------------------------------------------------------------
-- # DATE       | VERSION        | MODIFIED BY | DESCRIPTION
-- #-----------------------------------------------------------------------------------------------------------------
====================================================================================================================


ALTER FUNCTION [dbo].[ufn_stg_ProperBPartyNoExtra](@BPartyNo AS VARCHAR(MAX))RETURNS VARCHAR(32)
AS
BEGIN   
    DECLARE @Return VARCHAR(32);

    SET @Return = '';

    IF (LEN(@BPartyNo) > 0) 
        SELECT @Return = CASE WHEN LEFT(@BPartyNo, 4) = '+610' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)) ) ) ELSE
                                    CASE WHEN LEFT(@BPartyNo, 3) = '+61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)) ) ) ELSE
                                        CASE WHEN LEFT(@BPartyNo, 2) = '61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)) ) ) ELSE       
                                            CASE WHEN LEFT(@BPartyNo, 6) = '001161' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)) ) ) ELSE 
                                                CASE WHEN ( LEFT(@BPartyNo,2) = '01' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                    CASE WHEN ( LEFT(@BPartyNo,2) = '02' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                        CASE WHEN ( LEFT(@BPartyNo,2) = '03' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                            CASE WHEN ( LEFT(@BPartyNo,2) = '04' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                                CASE WHEN ( LEFT(@BPartyNo,2) = '07' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE 
                                                                    CASE WHEN ( LEFT(@BPartyNo,2) = '08' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo)) ELSE '' END
                                                                END
                                                            END
                                                        END
                                                    END
                                                END
                                            END
                                        END
                                    END
                        END;
    ELSE
        SELECT @Return = '';

    RETURN @Return
END

推荐答案

它们根本不需要嵌套:

   SELECT @Return = CASE WHEN LEFT(@BPartyNo, 4) = '+610' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)) ) )
                         WHEN LEFT(@BPartyNo, 3) = '+61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)) ) )
                         WHEN LEFT(@BPartyNo, 2) = '61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)) ) )
                         WHEN LEFT(@BPartyNo, 6) = '001161' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)) ) )
                         WHEN ( LEFT(@BPartyNo,2) = '01' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '02' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '03' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '04' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '07' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         WHEN ( LEFT(@BPartyNo,2) = '08' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
                         ELSE ''
                     END

就我个人而言，我会重组您的代码，以便您确保在输入时对数据进行消毒，而不是现在就尝试对其进行消毒(显然为时已晚......).或者至少在您的客户端语言中进行转换(即以任何调用这个 sproc)，这有望比 T-SQL 更适合字符串操作的任务.

Personally, I would restructure your code so that you make sure the data in sanitized upon input, rather than trying to sanitize it now (when it's clearly too late...). Or at least do the conversion in your client language (i.e. in whatever is calling this sproc), which is hopefully more suited to the task of string manipulation than T-SQL is.

这篇关于Case 表达式只能嵌套到 10 级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！