CODEWITHSUNDEEP
sql-serverstored-procedures
jtram
jtram

Reputation: 25

Combine multiple SQL statements into one stored procedure

Okay from these two tables:

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

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

SELECT DISTINCT 
    Sessions.SessionID, 
    COUNT(*) OVER (PARTITION BY SessionID) AS Sess
FROM 
    Sessions
WHERE 
    UserID = @UserId

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

SELECT *
FROM Sessions
WHERE UserId = @UserId

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.

Upvotes: 0

Views: 807

Answers (2)

wp78de
wp78de

Reputation: 18950

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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.

Upvotes: 1

Related Questions