不要在SQL Server中盲目地追求一句处理

DECLARE @a int

SET @a = 1

 

DECLARE @t TABLE(

    id int IDENTITY,

    a int, b int)

DECLARE @dt datetime, @loop int, @id int

SET @loop = 0

WHILE @loop < 5

BEGIN

    SET @loop = @loop + 1

    RAISERROR("test %d", 10, 1, @loop) WITH NOWAIT

    SET @dt = GETDATE()

        SELECT [ITEM] FROM A

        WHERE @a = 0

            AND [ITEM] < "A"

        UNION ALL

        SELECT [ItemNumber] FROM B

        WHERE @a = 1

            AND [ItemNumber] < "A"

    INSERT @t(a) VALUES(DATEDIFF(ms, @dt, GETDATE()))

    SELECT @id = SCOPE_IDENTITY(), @dt = GETDATE()

        IF @a = 0

            SELECT [ITEM] FROM A

            WHERE [ITEM] < "A"

        ELSE IF @a = 1

            SELECT [ItemNumber] FROM B

            WHERE [ItemNumber] < "A"

    UPDATE @t SET b = DATEDIFF(ms, @dt, GETDATE())

    WHERE id = @id

END

SELECT * FROM @t

UNION ALL

SELECT NULL, SUM(a), SUM(b) FROM @t

id  a       b

--- ------- -------

1   3410   2063

2   1703   1656

3   1763   1656

4   1800   1793

5   1643   1856

NULL   10319  9024