Stored Procedure to delete from 3 tables
I have three tables: classList, classUsers, classSignup
So, I want to write a stored procedure that allows the admin to delete a class and the procedure will also delete the records from the signup table for that class. The classID will be the only input for the SP and there will be no output.
The catch is that the users are dynamic. The connection from the Users table to the Signup table MAY be a one-to-many relationship or it may not. If a user is ONLY signed up for the one class that is being deleted, I need to delete that user as well.
Any help, whether code or just trying to think through this, is greatly appreciated.
Here's the simple bit of the SP that I have already written.
CREATE PROCEDURE sp_deleteClass
-- Declare variables for deleting from 3 tables
-- Delete class from classList table
DELETE FROM classList
WHERE classID = @classID
-- Use the classID to delete signups from table classSignup and users from table classUsers
DECLARE @userID [int]