代码之家 › 专栏 › 技术社区 › Plar625

SQL Server 2008 R2存储过程中的WHILE condition with sub IF语句?

sql-server-2008-r2 stored-procedures sql-server

Plar625 · 技术社区 · 7 年前

我在SQL Server 2008 R2的存储过程中获得了以下代码。

..
@currentAccID     char(21), 
@currentUserID    char(21),
@UserID1    char(21),
@UserID2    char(21),
@UserID3    char(21),
@Case     smallint, 

...
SELECT @UserID1 = UserID1, @UserID2 = UserID2, @UserID3 = UserID3 FROM OLD_Users WHERE oldAccID = @currentAccID

WHILE (@userid1 = @userid or @userid2 = @userid or @userid3 = @userid)
BEGIN
    IF (@Case = 10 OR @Case = 20) 
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_NormalUser'
    END
    ELSE IF (@Case = 12 OR @Case = 22)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_MediumUser'
    END
    ELSE IF (@Case = 13 OR @Case = 23)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_HighUser'
    END
    ELSE IF (@Case = 14 OR @Case = 24)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_UltraUser'
    END
END

WHILE (@userid1 != @userid or @userid2 != @userid or @userid3 != @userid)
BEGIN
    IF (@Case = 10 OR @Case = 20) 
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'NormalUser'
    END
    ELSE IF (@Case = 12 OR @Case = 22)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'MediumUser'
    END
    ELSE IF (@Case = 13 OR @Case = 23)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'HighUser'
    END
    ELSE IF (@Case = 14 OR @Case = 24)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'UltraUser'
    END
END

...
INSERT INTO NewUser (UserID,Case,Equipment ......) VALUES (@UserID,@Case,@Equipment .......)

上述代码是否正确?此外,还有第三种可能性 @currentAccID 与完全不匹配 oldAccID 从…起 OLD_Users 带有 SELECT 查询,但仍然应该可以,因为第二个 WHILE 语句将被执行,对吗?性能应该没有问题吧?我非常确信,使用 虽然 语句等,但我不擅长SQL存储过程。提前感谢您花时间来帮助我!

1 回复 | 直到 7 年前

Babu Swami 7 年前

那么你不应该使用 while 循环。它可以通过简单的 if-else 声明。请使用以下代码执行相同操作:

..
@currentAccID     char(21), 
@currentUserID    char(21),
@UserID1    char(21),
@UserID2    char(21),
@UserID3    char(21),
@Case     smallint, 

...
-- this condition checks whether @userid is present in any of the columns UseID1 or UserID2 or UserID3
IF EXISTS(SELECT NULL FROM OLD_Users WHERE oldAccID = @currentAccID AND (UserID1 = @userid OR UserID2 = @userid OR UserID3 = @userid))
BEGIN
    SET  @OldORNew = 1
    IF (@Case = 10 OR @Case = 20) 
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_NormalUser'
    END
    ELSE IF (@Case = 12 OR @Case = 22)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_MediumUser'
    END
    ELSE IF (@Case = 13 OR @Case = 23)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_HighUser'
    END
    ELSE IF (@Case = 14 OR @Case = 24)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'OLD_UltraUser'
    END
END
ELSE
BEGIN
    SET  @OldORNew = 0
    IF (@Case = 10 OR @Case = 20) 
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'NormalUser'
    END
    ELSE IF (@Case = 12 OR @Case = 22)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'MediumUser'
    END
    ELSE IF (@Case = 13 OR @Case = 23)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'HighUser'
    END
    ELSE IF (@Case = 14 OR @Case = 24)
    BEGIN
        SELECT TOP 1 @Equipment = Equipment FROM Users WHERE UserID = 'UltraUser'
    END
END

...
INSERT INTO NewUser (UserID,Case,Equipment ......) VALUES (@UserID,@Case,@Equipment .......)