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'
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
andCATCH
). If I ever get into one of those two cases@count
will never reach 11 and enter a loop infinity. A recommendation: avoid usingstrings
in theWHILE
, use aBIT
, since the value isTRUE
orFALSE
.– João Martins