Deadlock with SQL SERVER PROCEDURE

2

I need to generate a game table for four groups with 5 teams, for each day a total of games must be performed, at the time the PROCEDURE that performs the insertion starts is called the bank goes in loop, someone knows where is the error?

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'
    
asked by anonymous 18.09.2018 / 00:44

0 answers