关于重构SQL PROC的感触

重新写了一个存储过程，从之前的700行缩短为200行。。。。

1.对于首先能结束存储过程的分支，要把判断写在前面，有goto或者return的语句就不需要写else了。
2.一次设置多个参数的值，不需要重复写set，直接select并以逗号相隔就可以。
3.判断之前的select是否有值，用@@ROWCOUNT = 0来判断，不用变量是否为空判断（@business_acctid）
如：--检查当前手机号是否注册服务
    SELECT  @business_acctid = acct_id
    FROM    mbphone
    WHERE   phone = @current_phone
            AND country_code = @current_country_code

    IF @@ROWCOUNT = 0 AND @@ERROR = 0
        BEGIN
            select @error = 101,@msg = 'Not find Acctid. Current phone is not register.',@call_active_count = 0;
            GOTO result_end
        END
        
4.对于测试环境和正式环境一些配置参数不同的问题，可以把配置参数传进来。配置参数的取值在Service端（存储过程调用端）通过webconfig进行配置。
如：@use_type INT
5.使用连接查询时，on后面的条件是连接两个表的关键条件。其他条件放在where后面
如：SELECT  @access_number = speed_dial, @call_active_count = call_active_cnt
    FROM    speed_dial sp
            INNER JOIN access_number ac ON ac.access_number = sp.speed_dial
    WHERE   sp.TYPE = @speed_call_type
            AND sp.ACCTID = @business_acctid
            
6.某些分支，可以合并的尽量用“提取”的方法将条件精简。
如：AND ac.nation_code = @current_nation_code
            AND ( ( @current_nation_code = 'CA'
                    AND ac.city = @city
                  )
                  OR ( @current_nation_code <> 'CA' )
                )
7.随机排序可以直接加在最后。
如： ORDER BY NEWID() DESC

8.左联接 左表为主表，左表满足条件的行将列出，右表不能满足条件的用null填充。 因此只需要判断右表为null的行的左表的数据 就是没有使用过的。
如：--获取当前用户可选的AccessNumber(包含CA),随机获取
    SELECT TOP 1 @access_number = access_number
    FROM    access_number ac
            LEFT OUTER JOIN speed_dial sp ON ac.access_number = sp.speed_dial
                                                        AND sp.ACCTID = @business_acctid
                                                        AND sp.phone=@current_phone
    WHERE   ac.NUMBER_TYPE = @access_call_type
            AND ac.CLIENT_TYPE = 'IPHONE'
            AND ac.USE_TYPE = @use_type
            AND ac.COUNTRY_CODE = @current_country_code
            AND ac.nation_code = @current_nation_code
            AND ( ( @current_nation_code = 'CA'
                    AND ac.city = @city
                  )
                  OR ( @current_nation_code <> 'CA' )
                )
            AND sp.speed_dial IS NULL
    ORDER BY NEWID() DESC
    
9.多条件筛选可以考虑用多列排序解决。
如：ORDER BY call_active_cnt, LOADTIME ASC

10.transaction内只放insert，update，delete等语句，不放select语句。如果需要用到查询数据，可以在transaction外select到@变量中，判断是否需要更新时也可以设flag方式来判断。
如： IF @@ROWCOUNT = 1 AND @@ERROR = 0 -- 查找到了计数器为0的记录或者绑定时间最早的记录
                BEGIN
                    SET @unbind_flag = 1 --需要进行解绑操作
                END
            ELSE     --根据绑号规则，没有任何符合条件的号码提供绑定。
                BEGIN    
                    SELECT @error = 102,@call_active_count = 0,@msg = 'Bind Failure.Not Enough Access Number.'
                    GOTO result_end
                END
                
BEGIN TRY
        BEGIN TRANSACTION
        IF @unbind_flag = 1
            BEGIN
                --Add a unbind log
                INSERT  INTO speed_dial_log ( acctid, phone, country_code, dnis_out, d_country_code,
                                                         speed_dial, s_country_code, operate, operator )
                        SELECT  acctid, @current_phone, @current_country_code, dnis_out, d_country_code,
                                @log_access_number, s_country_code, 'Unbind', 'appAuto'
                        FROM    mb_account_speed_dial
                        WHERE   phone=@current_phone AND speed_dial = @log_access_number
                
                --解绑查找到的号码
                DELETE  FROM mb_account_speed_dial
                WHERE   phone=@current_phone AND speed_dial = @log_access_number
            END   
                
        ------------------Begin Bind New Number---------------------
        IF @access_number IS NULL
            BEGIN
                --获取需要绑定的速拨号码
                SET @access_number = @log_access_number
            END              
        INSERT  INTO mb_account_speed_dial ( ACCTID, SPEED_DIAL, S_COUNTRY_CODE, TYPE, DNIS_OUT, D_COUNTRY_CODE, NAME,
                                             LOADTIME, OP_ID, PHONE, P_COUNTRY_CODE )
        VALUES  ( @business_acctid, @access_number, @current_country_code, @speed_call_type, @out_phone,
                  @out_country_code, @name, GETDATE(), 'iPhone:' + RTRIM(LTRIM(STR(@business_acctid))), @current_phone,
                  @current_country_code )

        --Add a bind log
        INSERT  INTO mb_account_speed_dial_log ( acctid, phone, country_code, dnis_out, d_country_code,
                                                 speed_dial, s_country_code, operate, operator )
        VALUES  ( @business_acctid, @current_phone, @current_country_code, @out_phone, @out_country_code,
                  @access_number, @current_country_code, 'BindNew', 'app' );

        SELECT @call_active_count = 0,@error = 0,@msg = 'Bind New AccessNumber'
        IF @unbind_flag = 1
            BEGIN
                SET @msg = 'Update AccessNumber'
            END

        ------------------End Bind New Number---------------------                  
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @error = ERROR_NUMBER(),@call_active_count = 0,@msg = ERROR_MESSAGE()
    END CATCH

   

完整的存储过程代码：

-- =============================================
-- Author:        DannyWang
-- Create date: 2012-08-01
-- Update date: 2013-02-01
-- Description:    Dialer绑定速播回拨号码
-- Update: 重新整理SQL语句
-- =============================================
ALTER PROCEDURE bind_speed
    @current_phone VARCHAR(32) ,
    @current_country_code VARCHAR(10) ,
    @current_nation_code VARCHAR(10) ,
    @out_phone VARCHAR(64) ,
    @out_country_code VARCHAR(10) ,
    @name VARCHAR(64) ,
    @city VARCHAR(32) ,
    @operation VARCHAR(20) ,
    @use_type INT
AS 
    DECLARE @error INT = 0
    DECLARE @msg VARCHAR(100) = ''
    DECLARE @business_acctid INT = 0
    DECLARE @access_number VARCHAR(32)
    DECLARE @call_active_count INT
    DECLARE @access_call_type INT
    DECLARE @speed_call_type INT
    DECLARE @log_access_number VARCHAR(32)
    DECLARE @unbind_flag INT= 0

    IF @operation = 'speed_dial' --速直播
        BEGIN
            SELECT @access_call_type = 2, @speed_call_type = 0 
        END
    ELSE 
        BEGIN
            SELECT @access_call_type = 3, @speed_call_type = 1 
        END

    --检查当前手机号是否注册服务
    SELECT  @business_acctid = acct_id
    FROM    phone_subscribe
    WHERE   phone = @current_phone
            AND country_code = @current_country_code

    IF @@ROWCOUNT = 0 AND @@ERROR = 0
        BEGIN
            select @error = 101,@msg = 'Not find Acctid. Current phone is not register.',@call_active_count = 0;
            GOTO result_end
        END

    --手机号合法，检查之前是否有绑定关系
    SELECT  @access_number = speed_dial, @call_active_count = call_active_cnt
    FROM    speed_dial sp
            INNER JOIN access_number ac ON ac.access_number = sp.speed_dial
    WHERE   sp.TYPE = @speed_call_type
            AND sp.ACCTID = @business_acctid
            AND sp.DNIS_OUT = @out_phone
            AND sp.D_COUNTRY_CODE = @out_country_code
            AND ( ( sp.PHONE = @current_phone
                    AND sp.P_COUNTRY_CODE = @current_country_code
                  )
                  OR ( RTRIM(LTRIM(sp.PHONE)) = ''
                       AND RTRIM(LTRIM(sp.P_COUNTRY_CODE)) = ''
                     )
                )
            AND ac.client_type = 'IPHONE'
                      
    IF @@ROWCOUNT > 0 AND @@ERROR = 0 --之前有绑定关系
        BEGIN
            SELECT @error = 0,@msg = 'This Number have binded.'
            GOTO result_end;
        END
            
    --获取当前用户可选的AccessNumber(包含CA),随机获取
    SELECT TOP 1 @access_number = access_number
    FROM    access_number ac
            LEFT OUTER JOIN speed_dial sp ON ac.access_number = sp.speed_dial
                                                        AND sp.ACCTID = @business_acctid
                                                        AND sp.phone=@current_phone
    WHERE   ac.NUMBER_TYPE = @access_call_type
            AND ac.CLIENT_TYPE = 'IPHONE'
            AND ac.USE_TYPE = @use_type
            AND ac.COUNTRY_CODE = @current_country_code
            AND ac.nation_code = @current_nation_code
            AND ( ( @current_nation_code = 'CA'
                    AND ac.city = @city
                  )
                  OR ( @current_nation_code <> 'CA' )
                )
            AND sp.speed_dial IS NULL
    ORDER BY NEWID() DESC 
                        
    IF @@ROWCOUNT = 0--没有满足条件的接入号码
        BEGIN
            -- 查找之前绑定关系中计数器为0或者绑定时间最早的记录
            SELECT TOP 1 @log_access_number = speed_dial
            FROM    speed_dial sp
                    INNER JOIN access_number ac ON sp.speed_dial = ac.access_number
            WHERE   sp.type = @speed_call_type
                    AND sp.acctid = @business_acctid AND sp.phone = @current_phone
                    AND ac.nation_code = @current_nation_code
                    AND ( ( @current_nation_code = 'CA'
                            AND ac.city = @city
                          )
                          OR ( @current_nation_code <> 'CA' )
                        )
                    AND ac.client_type = 'IPHONE'
                    AND ac.USE_TYPE = @use_type
                    AND ac.NUMBER_TYPE = @access_call_type
            ORDER BY call_active_cnt, LOADTIME ASC
            
            IF @@ROWCOUNT = 1 AND @@ERROR = 0 -- 查找到了计数器为0的记录或者绑定时间最早的记录
                BEGIN
                    SET @unbind_flag = 1 --需要进行解绑操作
                END
            ELSE     --根据绑号规则，没有任何符合条件的号码提供绑定。
                BEGIN    
                    SELECT @error = 102,@call_active_count = 0,@msg = 'Bind Failure.Not Enough Access Number.'
                    GOTO result_end
                END
        END
                
    BEGIN TRY
        BEGIN TRANSACTION
        IF @unbind_flag = 1 
            BEGIN
                --Add a unbind log
                INSERT  INTO speed_dial_log ( acctid, phone, country_code, dnis_out, d_country_code,
                                                         speed_dial, s_country_code, operate, operator )
                        SELECT  acctid, @current_phone, @current_country_code, dnis_out, d_country_code,
                                @log_access_number, s_country_code, 'Unbind', 'appAuto'
                        FROM    speed_dial
                        WHERE   phone=@current_phone AND speed_dial = @log_access_number
                
                --解绑查找到的号码
                DELETE  FROM speed_dial
                WHERE   phone=@current_phone AND speed_dial = @log_access_number
            END   
                
        ------------------Begin Bind New Number---------------------
        IF @access_number IS NULL 
            BEGIN
                --获取需要绑定的速拨号码
                SET @access_number = @log_access_number
            END              
        INSERT  INTO speed_dial ( ACCTID, SPEED_DIAL, S_COUNTRY_CODE, TYPE, DNIS_OUT, D_COUNTRY_CODE, NAME,
                                             LOADTIME, OP_ID, PHONE, P_COUNTRY_CODE )
        VALUES  ( @business_acctid, @access_number, @current_country_code, @speed_call_type, @out_phone,
                  @out_country_code, @name, GETDATE(), 'iPhone:' + RTRIM(LTRIM(STR(@business_acctid))), @current_phone,
                  @current_country_code )

        --Add a bind log
        INSERT  INTO speed_dial_log ( acctid, phone, country_code, dnis_out, d_country_code,
                                                 speed_dial, s_country_code, operate, operator )
        VALUES  ( @business_acctid, @current_phone, @current_country_code, @out_phone, @out_country_code,
                  @access_number, @current_country_code, 'BindNew', 'app' );

        SELECT @call_active_count = 0,@error = 0,@msg = 'Bind New AccessNumber'
        IF @unbind_flag = 1 
            BEGIN
                SET @msg = 'Update AccessNumber'
            END

        ------------------End Bind New Number---------------------                  
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @error = ERROR_NUMBER(),@call_active_count = 0,@msg = ERROR_MESSAGE()
    END CATCH
                
    RESULT_END:

    SELECT  @error AS ERROR, @msg AS msg, @access_number AS access_number, @call_active_count AS call_active_count
GO