Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: Adding Delete to Stored Procedures

    I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out


    Code:
    REATE PROCEDURE InsertTerms
    AS
    INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
    	 [FirstName],
    	 [LastName],
    	 [SocialSecurityNumber],
    	 [DateHired],
    	 [Status],
    	 [Title],
    	 [DepartmentName],
    	[Pictures]) 
    
    SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
    FROM  EmployeeGamingLicense AS a
    WHERE a.STATUS = 'TERMINATED'
    IF @@Error <> '0'
    RETURN
    
    
    GO

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    I dont believe that a simple answer like
    DELETE FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATED'
    is what you are looking at. Can u please give us some more details on what is expected?

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    From the way your question is phrased, it sounds like you want to insert from tableA to tableB then if that was successful delete from tableA. If so:

    Code:
     
    declare @err int
    BEGIN TRAN
    INSERT INTO TableB (col1, col2, col3, etc)
    SELECT col1, col2, col3, etc)
    FROM TableA
    WHERE condition
     
    SELECT @err = @@error
    IF @err <> 0
    BEGIN
       ROLLBACK
       RETURN @err
    END
     
    DELETE FROM TableA
    WHERE condition
     
    SELECT @err = @@error
    IF @err <> 0
    BEGIN
       ROLLBACK
       RETURN @err
    END
     
    COMMIT

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ok Heres what I got

    I am trying to delete the data from the EmployeeGamingLicense table after its been inserted into the Termination table


    Code:
    ALTER  PROCEDURE InsertTerms
    AS
    INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
    	 [FirstName],
    	 [LastName],
    	 [SocialSecurityNumber],
    	 [DateHired],
    	 [Status],
    	 [Title],
    	 [DepartmentName],
    	[Pictures]) 
    
    SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
    DELETE FROM EmployeeGamingLicense o
     WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
    IF @@Error <> '0'
    
    RETURN
    
    GO
    Last edited by desireemm; 09-01-06 at 14:52.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    inserted is a virtual table only accessable via a trigger, which does not fit with your ALTER proc.

    Why do a write just to have to do a select with the "If Exists". Don't make it more complicated than necessary.

    This is one of the prime examples of a transaction ... a unit of work that must succeed or fail as a unit. If the insert works and then the server crashes, you have a duplicate entry in two tables. The BEGIN TRAN ... COMMIT block ensures consistency between the two tables ... it either exists in one or the other, thanks to the COMMIT or ROLLBACK.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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