存储过程

Question

我有一个我想改进的存储过程。我已经更改了DB名称和过程名称。我正在研究Server 2000。

 USE [DBName]

ALTER PROCEDURE [dbo].[Table_GetSomething] 
    @name nvarchar(50), 
    @Cooltree nvarchar(10),
    @aID int,
    @bID int = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @bCE bit = 0
declare @aCE bit = 0
declare @bCE2 bit = 0
declare @aCE2 bit = 0
declare @tableName varchar(60)
declare @table table
(
    FA nvarchar(255),
    FN nvarchar(255),
    [Seeboj] nvarchar(1024),
    Brotty nvarchar(4000),
    CD nvarchar(4000)
)

if( select count(*)
    from ECCB
    where aID = @aID
    and bID = @bID
    and [Name] = @name) = 1
    set @bCE = 1
else
    if( select count(*)
    from ECCA
    where aID = @aID
    and [Name] = @name) > 0
    set @aCE = 1

if( select count(*)
    from ECCDB
    where aID = @aID
    and bID = @bID) > 0
    set @bCE2 = 1
else
    if( select count(*)
    from ECCDA
    where aID = @aID) > 0
    set @aCE2 = 1


if @bCE = 1
    insert into @table (FA, FN, [Seeboj], Brotty)
        select FA, FN, [Seeboj], Brotty
        from ECCB
        where aID = @aID
        and bID = @bID
        and [Name] = @name
else
    if(@aCE = 1)
        insert into @table (FA, FN, [Seeboj], Brotty)
            select FA, FN, [Seeboj], Brotty
            from ECCA
            where aID = @aID
                and [Name] = @name
    else
        insert into @table (FA, FN, [Seeboj], Brotty)
            select FA, FN, [Seeboj], Brotty
            from EDC
            where Cooltree = @Cooltree
            and [Name] = @name

if(@bCE2 = 1)
    update @table
    set CD =
        (select CD
         from ECCDB
         where aID = @aID
         and bID = @bID)
else
    if(@aCE2 = 1)
        update @table
        set CD =
            (select CD
             from ECCDA
             where aID = @aID)
    else
        update @table
        set CD =
            (select CD
             from EDCCD
             where Cooltree = @Cooltree)

select * from @table
END

Answer 1

您的SQL中有很多if语句。这让我感到震惊的是，您在SQL中做了一些应该在应用程序中完成的事情，并且这个SQL应该被分离成两个不同的存储过程。

这样做的好处是：

1. 更快的存储过程
2. 使用编码语言而不是查询语言来完成本来要完成的逻辑，在类似C#或VB这样的东西中做逻辑就容易多了。

你需要把你想要做的作为一个整体来看，因为这看起来并不是什么有趣的事情。

你可以去掉所有这些变量

declare @bCE bit = 0
declare @aCE bit = 0
declare @bCE2 bit = 0
declare @aCE2 bit = 0
declare @tableName varchar(60)

我没有看到在任何地方使用的@tableName变量。

您可以将这些操作与检查合并，而不是有两组if然后语句，如下所示

IF(SELECT COUNT(*)
    FROM ECCB
    WHERE aID = @aID
        AND bID = @bID
        AND [Name] = @name) = 1
BEGIN
    INSERT INTO @table (FA, FN, [Seeboj], Brotty)
    SELECT FA, FN, [Seeboj], Brotty
    FROM ECCB
    WHERE aID = @aID
        AND bID = @bID
        AND [Name] = @name
END
ELSE
BEGIN
    IF(SELECT COUNT(*)
        FROM ECCA
        WHERE aID = @aID
            AND [Name] = @name) > 0
    BEGIN
        INSERT INTO @table (FA, FN, [Seeboj], Brotty)
        SELECT FA, FN, [Seeboj], Brotty
        FROM ECCA
        WHERE aID = @aID
            AND [Name] = @name
    END
    ELSE
    BEGIN
        INSERT INTO @table (FA, FN, [Seeboj], Brotty)
        SELECT FA, FN, [Seeboj], Brotty
        FROM EDC
        WHERE Cooltree = @Cooltree
            AND [Name] = @name
    END
END


IF(SELECT COUNT(*)
    FROM ECCDB
    WHERE aID = @aID
        AND bID = @bID) > 0
BEGIN
    UPDATE @table
    SET CD =
        (SELECT CD
        FROM ECCDB
        WHERE aID = @aID
            AND bID = @bID)
END
ELSE
BEGIN
    IF(SELECT COUNT(*)
        FROM ECCDA
        WHERE aID = @aID) > 0
    BEGIN
        UPDATE @table
        SET CD =
            (SELECT CD
            FROM ECCDA
            WHERE aID = @aID)
    END
    ELSE
    BEGIN
        UPDATE @table
        SET CD =
            (SELECT CD
            FROM EDCCD
            WHERE Cooltree = @Cooltree)
    END
END

您可能已经注意到，我还为不同的块添加了BEGIN和END语句，我认为这增加了代码的可读性，甚至可能是必要的，我不确定在SERVER 2000中，我通常在2008年编写代码。

我还大写了所有有趣的SQL SERVER关键字，除了可读性之外，这实际上并不重要，但我认为将这些单词大写是标准做法，即使SQL SERVER在关键字方面不区分大小写。

经过我所做的所有修改，结果如下所示：

USE [DBName]

ALTER PROCEDURE [dbo].[Table_GetSomething] 
    @name NVARCHAR(50), 
    @Cooltree NVARCHAR(10),
    @aID INT,
    @bID INT = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @table TABLE
    (
        FA NVARCHAR(255),
        FN NVARCHAR(255),
        [Seeboj] NVARCHAR(1024),
        Brotty NVARCHAR(4000),
        CD NVARCHAR(4000)
    )

    IF(SELECT COUNT(*)
        FROM ECCB
        WHERE aID = @aID
            AND bID = @bID
            AND [Name] = @name) = 1
    BEGIN
        INSERT INTO @table (FA, FN, [Seeboj], Brotty)
        SELECT FA, FN, [Seeboj], Brotty
        FROM ECCB
        WHERE aID = @aID
            AND bID = @bID
            AND [Name] = @name
    END
    ELSE
    BEGIN
        IF(SELECT COUNT(*)
            FROM ECCA
            WHERE aID = @aID
                AND [Name] = @name) > 0
        BEGIN
            INSERT INTO @table (FA, FN, [Seeboj], Brotty)
            SELECT FA, FN, [Seeboj], Brotty
            FROM ECCA
            WHERE aID = @aID
                AND [Name] = @name
        END
        ELSE
        BEGIN
            INSERT INTO @table (FA, FN, [Seeboj], Brotty)
            SELECT FA, FN, [Seeboj], Brotty
            FROM EDC
            WHERE Cooltree = @Cooltree
                AND [Name] = @name
        END
    END


    IF(SELECT COUNT(*)
        FROM ECCDB
        WHERE aID = @aID
            AND bID = @bID) > 0
    BEGIN
        UPDATE @table
        SET CD =
            (SELECT CD
            FROM ECCDB
            WHERE aID = @aID
                AND bID = @bID)
    END
    ELSE
    BEGIN
        IF(SELECT COUNT(*)
            FROM ECCDA
            WHERE aID = @aID) > 0
        BEGIN
            UPDATE @table
            SET CD =
                (SELECT CD
                FROM ECCDA
                WHERE aID = @aID)
        END
        ELSE
        BEGIN
            UPDATE @table
            SET CD =
                (SELECT CD
                FROM EDCCD
                WHERE Cooltree = @Cooltree)
        END
    END
SELECT * FROM @table
END