Wednesday 22 May 2013

Code Horrors

I just stumbled across a strange piece of code, albeit a small one. I thought I would create a blog post to start collecting these gems.
 
Item 1
 
SELECT
    @RowIndex = RowID
FROM
    CBT
WHERE
    RowID = 1;
 
which actually means
 
SELECT @RowIndex = 1;
 
Item 2
 
UPDATE
    #Test
SET
    Ranking = NULL;
 
DECLARE
    @ID INT,
    @MemberID INT,
    @PrevMemberID INT = -1,
    @Ranking INT = 1;
 
WHILE 1 = 1
BEGIN
    SELECT TOP 1
        @ID = ID,
        @MemberID = MemberID
    FROM
        #Test
    WHERE
        Ranking IS NULL
    ORDER BY
        MemberID,
        Score DESC;
 
    IF @@ROWCOUNT = 0
        BREAK;
 
    IF @PrevMemberID <> @MemberID
        SET @Ranking = 1;
 
    UPDATE
        #Test
    SET
        Ranking = @Ranking
    WHERE
        ID = @ID;
 
    SET @PrevMemberID = @MemberID;
 
    SET @Ranking = @Ranking + 1;
END;
 
which actually means
 
WITH CTE AS (
    SELECT
        ID,
        ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY Score DESC) AS Ranking
    FROM
        #Test)
UPDATE
    t
SET
    Ranking = t.Ranking
FROM
    #Test t
INNER JOIN
    CTE a
        ON a.ID = t.ID;
 
 

2 comments:

  1. SELECT
    @RowIndex = RowID
    FROM
    CBT
    WHERE
    RowID = 1;


    Unless the table is empty and then it'd be NULL.

    ReplyDelete
  2. Agreed, however in the context of this code having an empty table did not change the behavior. The developer was literally trying to set @RowIndex to 1.

    ReplyDelete