CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4334

Changing a WHILE loop method to set-based logic

I have a stored procedure below that determines a fixtures table based on the parameters passed through when executing the produce:

'LL' means last 16 teams
'QF' means last 8 teams
'SF' means last 4 teams
'FF' means last 2 teams

Virtually losing teams are knocked out to shorten the teams down for each stage. The procedure below works but virtually I want to see if I can replace a the three WHILE LOOPS to something more set base and see if anyone knows how to manipulate the code to include this?

Below is the procedure:

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[InsertFixture_EUCompetition]
    -- Add the parameters for the stored procedure here
    -- exec InsertFixture_EUCompetition 1
    @SeasonID INT,
    @MatchType CHAR(2)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @WeekNumber INT
    DECLARE @FixtureDate DATE
    DECLARE @HomeTeamID INT
    DECLARE @AwayTeamID INT
    DECLARE @LeagueID INT
    -- Insert statements for procedure here
    SELECT * INTO #TempCompetition FROM [dbo].[EUCompetition] where SeasonID = @SeasonID
    -- SELECT * FROM  [dbo].[EUCompetition] where SeasonID = 1

    set @WeekNumber = 1
    set @FixtureDate = GETDATE()

     WHILE((SELECT COUNT(1) FROM #TempCompetition) > 0)
     BEGIN   

     SELECT @LeagueID =  MAX(LeagueID) from #TempCompetition

     IF @MatchType = 'LL'
     BEGIN
     INSERT EUFixture ( MatchType, WeekNumber, FixtureDate, HomeTeamID, HomeScore, AwayTeamID, AwayScore, HomeTeamResult, AwayTeamResult, LeagueID, CurrentSeason) 
     VALUES ( @MatchType, @WeekNumber , @FixtureDate,
      ( Select  TeamId from #TempCompetition where Position = 1 and LeagueID = @LeagueID ) , null,
      ( Select TeamId from #TempCompetition where Position = 4 and LeagueID = @LeagueID ) , null,
      null, null, @LeagueID , @SeasonID )

       INSERT EUFixture (MatchType, WeekNumber, FixtureDate, HomeTeamID, HomeScore, AwayTeamID, AwayScore, HomeTeamResult, AwayTeamResult, LeagueID, CurrentSeason) 
     VALUES (  @MatchType, @WeekNumber , @FixtureDate,
      ( Select TeamId from #TempCompetition where Position = 2 and LeagueID = @LeagueID ) , null,
      ( Select TeamId from #TempCompetition where Position = 3 and LeagueID = @LeagueID ) , null,
      null, null, @LeagueID , @SeasonID )
    RETURN
    END

    DELETE #TempCompetition WHERE LeagueID = @LeagueID
    set @WeekNumber  = @WeekNumber  + 1
    set @FixtureDate = DATEADD(day,1,@FixtureDate)
     END

    DROP TABLE #TempCompetition

    DECLARE @I INT

    IF @MatchType = 'QF'
    SET @I = 1
    WHILE(@I < 5)
    BEGIN
    set @WeekNumber  = @WeekNumber  + 1
    set @FixtureDate = DATEADD(day,1,@FixtureDate)
     INSERT EUFixture (MatchType, WeekNumber, FixtureDate, HomeTeamID, HomeScore, AwayTeamID, AwayScore, HomeTeamResult, AwayTeamResult, LeagueID, CurrentSeason) 
      VALUES (  'QF' , @WeekNumber, @FixtureDate , NULL, NULL,NULL,NULL,NULL,NULL,NULL,@SeasonID )
     SET @I = @I +1
    END


    IF @MatchType = 'SF'
    SET @I = 1
    WHILE(@I < 3)
    BEGIN
    set @WeekNumber  = @WeekNumber  + 1
    set @FixtureDate = DATEADD(day,1,@FixtureDate)
     INSERT EUFixture (MatchType, WeekNumber, FixtureDate, HomeTeamID, HomeScore, AwayTeamID, AwayScore, HomeTeamResult, AwayTeamResult, LeagueID, CurrentSeason) 
      VALUES (  'SF' , @WeekNumber, @FixtureDate , NULL, NULL,NULL,NULL,NULL,NULL,NULL,@SeasonID )
     SET @I = @I +1
    END


    IF @MatchType = 'FF'
    SET @I = 1
    WHILE(@I < 2)
    BEGIN
    set @WeekNumber  = @WeekNumber  + 1
    set @FixtureDate = DATEADD(day,1,@FixtureDate)
     INSERT EUFixture (MatchType, WeekNumber, FixtureDate, HomeTeamID, HomeScore, AwayTeamID, AwayScore, HomeTeamResult, AwayTeamResult, LeagueID, CurrentSeason) 
      VALUES (  'FF' , @WeekNumber, @FixtureDate , NULL, NULL,NULL,NULL,NULL,NULL,NULL,@SeasonID )
     SET @I = @I +1
    END


END

Thank you very much

Upvotes: 0

Views: 59

Answers (1)

Anand
Anand

Reputation: 1123

Try something like this:

create procedure dbo.InsertAwesome (
    @SeasonID INT,
    @MatchType CHAR(2)
    )
as
begin
set nocount on
insert EUFixture (
    MatchType,
    WeekNumber,
    FixtureDate,
    HomeTeamID,
    AwayTeamID,
    LeagueID,
    CurrentSeason
    )
select @MatchType,
    row_number() over (order by LeagueID) as WeekNumber,
    dateadd(dd, row_number() over (order by LeagueID), getdate()) as FixtureDate,
    min(TeamId),
    max(Teamed),
    LeagueID,
    @SeasonID
from    EUCompetition
where SeasonID = @SeasonID
group by LeagueID,
    case when Position in (1, 4) then 1 else 2 end

—You can do the same with the rest of the queries

end

Upvotes: 1

Related Questions