Dead Lock with SQL SERVER PROCEDURE

Asked

Viewed 59 times

2

I need to generate a table of games for four groups with 5 teams, for each day a total of games must be held, at the time that the PROCEDURE which executes the insertion starts is called the database loops, someone knows where the error is?

CREATE PROCEDURE sp_insere_jogos (@grupoA VARCHAR(10), @grupoB VARCHAR(10), @date DATE, @saida VARCHAR(10) OUTPUT)
AS
DECLARE @codTeamA INT
DECLARE @codTeamB INT
DECLARE @validate VARCHAR(10)
DECLARE@count INT

SET @validate = 'FALSE'
SET @count = 1

WHILE(@validate != 'TRUE')
BEGIN
    WHILE(@count < 11)
    BEGIN
        BEGIN TRY
            IF(@grupoA = 'grupoA')
            BEGIN
                SET @codTeamA = (SELECT codTeam FROM grupoA WHERE numTime = (CAST(RAND() * 5 AS INT)))
            END
            ELSE
            BEGIN
                IF(@grupoA = 'grupoB')
                BEGIN
                    SET @codTeamA = (SELECT codTeam FROM grupoB WHERE numTime = (CAST(RAND() * 5 AS INT)))
                END
                ELSE
                BEGIN
                    IF(@grupoA = 'grupoC')
                    BEGIN
                        SET @codTeamA = (SELECT codTeam FROM grupoC WHERE numTime = (CAST(RAND() * 5 AS INT)))
                    END
                END
            END
            IF(@grupoB = 'grupoB')
            BEGIN
                SET @codTeamB = (SELECT codTeam FROM grupoB WHERE numTime = (CAST(RAND() * 5 AS INT)))
            END
            ELSE
            BEGIN
                IF(@grupoB = 'grupoC')
                BEGIN
                    SET @codTeamB = (SELECT codTeam FROM grupoC WHERE numTime = (CAST(RAND() * 5 AS INT)))
                END
                ELSE
                BEGIN
                    IF(@grupoA = 'grupoD')
                    BEGIN
                        SET @codTeamB = (SELECT codTeam FROM grupoD WHERE numTime = (CAST(RAND() * 5 AS INT)))
                    END
                END
            END
            --END QUERY GET CODTEAM
            --BEGIN INSERT GAMES
            IF((SELECT COUNT(*) FROM jogos) = 0)
            BEGIN
                INSERT INTO jogos(codTeamA, codTeamB, data_jogo) VALUES(@codTeamA, @codTeamB, @date)
                SET @count = @count + 1
            END
            ELSE
            BEGIN
                IF((SELECT COUNT(@codTeamA) FROM jogos WHERE data_jogo = @date AND codTeamA = @codTeamA) = 0 
                    AND (SELECT COUNT(@codTeamB) FROM jogos WHERE data_jogo = @date AND codTeamB = @codTeamB) = 0)
                BEGIN
                    INSERT INTO jogos(codTeamA, codTeamB, data_jogo) VALUES(@codTeamA, @codTeamB, @date)
                    SET @count = @count + 1
                END
                ELSE
                BEGIN
                    PRINT('TIME JÁ JOGOU HOJE')
                END
            END
        END TRY
        BEGIN CATCH
            PRINT('ERRO AO ADICIONAR JOGO')
        END CATCH
    END
    SET @validate = 'TRUE'
END

SET @saida = 'TRUE'
  • 2

    I didn’t fully understand the structure of SP, but the variable @count is only being incremented in two cases, another two being left out (ELSE and CATCH). If I ever get into one of those two cases @count will never reach 11 and enter a loop infinity. A recommendation: avoid using strings in the WHILE, use a BIT, since the value is TRUE or FALSE.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.