SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Zealot Taranis's Avatar
    Join Date
    Aug 2002
    Location
    Franklin, TN
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Procedure to delete from 3 tables

    I have three tables: classList, classUsers, classSignup

    classList
    ------------
    classID
    name
    date
    etc...

    classUsers
    ------------
    userID
    fName
    lName
    etc.....

    classSignup
    ------------
    classID
    userID
    notify

    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.

    Code:
    CREATE PROCEDURE sp_deleteClass
    
    -- Declare variables for deleting from 3 tables
    
    (@classID [int]) 
    
    AS 
    
    -- 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]
    
    
    
    RETURN
    GO

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    man, it's been years since i wrote a stored proc...
    Code:
    select C1.userID
      ,	count(C2.classID) as userclasses
      from classSignup C1
    inner
      join classSignup C2
      on   C1.userID = C2.userID
     where C1.classID = @class
    group
      by   C1.userID
    this gives you all the users taking the given class, and the total number of classes that each user is taking

    now loop (i forget how) through that result set, and for each userid, if userclasses=1 then
    Code:
    delete
      from classUsers
     where userID = userid in loop
    and when that's done,
    Code:
    delete
      from classSignup
     where classID = @class
    rudy
    http://r937.com/

  3. #3
    SitePoint Zealot Taranis's Avatar
    Join Date
    Aug 2002
    Location
    Franklin, TN
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That makes perfect sense. I, too, forgot that you could loop inside the Stored Procedure. Because it refreshed my memory, might refresh yours and might help other reading this thread:

    WHILE, which can be used to create loops inside a stored procedure. Its syntax is the following:

    WHILE boolean-expression
    Statement-block
    [BREAK]
    [[Statement-block]
    [CONTINUE]]

    The WHILE construct repeats its block of instructions until the expression boolean-expression becomes false. This means that you can create infinite loops inside stored procedures. Usually, you can stop an infinite loop by closing the database connection that initiated it, even though doing so may lead to locking problems within the database's tables.
    The BREAK and CONTINUE keywords can be used to forcibly exit the loop and short-circuit it, respectively. BREAK will cause the loop to conclude and will move the execution pointer to the first instruction after the WHILE statement. CONTINUE will force the execution pointer to move to the first statement inside the WHILE block, therefore restarting the next instance of the loop. You can generally use both instructions in response to an if-then-else condition (that is, "if we are finished with the analysis, end the loop").

    If anyone's interested, I'll post the finished procedure just for reference (that is, once I get it working).

    Thanks for the nudge and the code!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •