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'