Combine multiple SQL statements into one stored procedure-CodePudding

Okay from these two tables:

SELECT *
FROM Sessions
JOIN RegistrantSessions ON Sessions.ID = RegistrantSessions.SessionID

I need to count how many seats are taken (counting how many of each sessionID exist)

SELECT DISTINCT 
    RegistrantSessions.SessionID, 
    COUNT(*) OVER (PARTITION BY SessionID) AS SeatCount
FROM 
    RegistrantSessions
WHERE 
    EventID = @EventId

And then if the count SeatCount is less than SeatLimit (an int column in Sessions table), select only those sessions from my join (below code is not correct):

SELECT *
FROM Sessions
WHERE EventId = @EventId 
  AND SessionTime = @SessionTime 
  AND SessionType = @SessionType 
  AND Active = @SessionActive

How do I combine all of these statements into one stored procedure?

Each select statement works on its own, but I don't know how to combine this into one solution.

ALTER PROCEDURE [dbo].[spGetSessionList]
    @EventId int,
    @SessionTime datetime,
    @SessionType nvarchar(1),
    @SessionActive bit
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM Sessions
    JOIN RegistrantSessions ON Sessions.ID = RegistrantSessions.SessionID

    SELECT DISTINCT 
        RegistrantSessions.SessionID, 
        COUNT(*) OVER (PARTITION BY SessionID) AS SeatCount
    FROM 
        RegistrantSessions
    WHERE 
        EventID = @EventId
  
    SELECT *
    FROM Sessions
    WHERE EventId = @EventId 
      AND SessionTime = @SessionTime 
      AND SessionType = @SessionType 
      AND Active = @SessionActive
END

CodePudding user response：

WITH EligibleSessions As (
    SELECT s.ID
    FROM Sessions s
    JOIN RegistrantSessions rs ON s.ID = rd.SessionID
    WHERE rs.EventID = @EventID
    GROUP BY s.ID, s.SeatLimit
    HAVING COUNT(rs.*) < s.SeatLimit
)
SELECT s.*
FROM Sessions 
INNER JOIN EligibleSessions es on es.ID = s.ID
WHERE s.EventId = @EventId 
      AND s.SessionTime = @SessionTime 
      AND s.SessionType = @SessionType 
      AND s.Active = @SessionActive

There seems like an extra layer of nesting here, but I didn't want to have to include every field in the Sessions table in the GROUP BY.

You may need to repeat some of the WHERE conditions in the CTE. It's also weird to me for the EventID field to repeated in both Sessions and RegistrantSessions. Seems like an indication something is not normalized properly.

CodePudding user response：

If I understand your table structure correctly, we could join a the count query as subselect too:

SELECT *
FROM Sessions
JOIN (
    SELECT DISTINCT RegistrantSessions.SessionID
        ,COUNT(*) OVER (PARTITION BY SessionID) AS SeatCount
    FROM RegistrantSessions
    JOIN RegistrantSessions ON Sessions.ID = RegistrantSessions.SessionID
    WHERE EventID = @EventId
    ) AS regCounts ON regCounts ON Sessions.ID = regCounts.SessionID
WHERE Sessions.SeatLimit < regCounts.SeatCount
    AND EventId = @EventId
    AND SessionTime = @SessionTime
    AND SessionType = @SessionType
    AND Active = @SessionActive