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

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

GOI 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?|||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:


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|||I am trying to delete the data from the EmployeeGamingLicense table after its been inserted into the Termination table

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|||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.