asked on

SQL Stored Procedures and Result Sets

I am an average user of SQL and am working on a stored procedure that will delete records from multiple tables based on the criteria pulled from another table. I know how to format the DELETE SQL using the WHERE Clause and IN but was wondering if there is a way to return the ids into a "result set" and use that in my IN so I'm not executing the SELECT in the WHERE clause for each table. Here is my stored procedure which may make my question easier to understand (I haven't tested it yet so there may be syntax errors). Any help is greatly appreciated!

IF OBJECT_ID ('PurgeCompletedCVLs', 'P' ) IS NOT NULL
DROP PROCEDURE PurgeCompletedCVLs;
GO

CREATE PROCEDURE PurgeCompletedCVLs
AS
SET ANSI NULLS ON
SET QUOTED_IDENTIFIER ON
GO

BEGIN TRANSACTION
DELETE FROM Proof_Submitter WHERE ProofID IN (SELECT ProofID FROM Proof_Fax WHERE IsCompleted = True AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE()))
      IF @@ERROR <> 0
      BEGIN
            RAISERROR (‘DELETE FROM Proof_Submitter ERROR’, 16,1)
            GOTO _ROLLBACK
      END
DELETE FROM Proof_Vehicles WHERE FormID IN (SELECT ProofID FROM Proof_Fax WHERE IsCompleted = True AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE()))
IF @@ERROR <> 0
      BEGIN
            RAISERROR (‘DELETE FROM Proof_Vehicles ERROR’, 16,1)
            GOTO _ROLLBACK
      END
DELETE FROM Proof_Policy WHERE PolicyID IN (SELECT ProofID FROM Proof_Fax WHERE IsCompleted = True AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE()))
IF @@ERROR <> 0
      BEGIN
            RAISERROR (‘DELETE FROM Proof_Policy ERROR’, 16,1)
            GOTO _ROLLBACK
      END
DELETE FROM Proof_Fax WHERE IsCompleted = True AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE()))
IF @@ERROR <> 0
      BEGIN
            RAISERROR (‘DELETE FROM Proof_Submitter ERROR’, 16,1)
            GOTO _ROLLBACK
      END
_COMMIT:
IF @@TRANCOUNT <> 0
      BEGIN
            COMMIT
            GOTO _DONE
      END
_ROLLBACK:
IF @@TRANCOUNT <> 0
      BEGIN
            ROLLBACK
            GOTO _DONE
      END
_DONE:

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

you may need to change this line
>SELECT ProofID FROM Proof_Fax WHERE IsCompleted = True AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE())

SELECT ProofID FROM Proof_Fax WHERE IsCompleted = 'True' AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE())

or like this depending what value you store on IsCompleted field

SELECT ProofID FROM Proof_Fax WHERE IsCompleted = 1 AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE())

Jared_S

You could create a temporary table or variable table with the ProofID's and use an In statement, but I think this would be more efficient:

DELETE FROM Proof_Submitter /* repeat for each table */
WHERE EXISTS
( select null
from Proof_Fax customers
where Proof_Submitter.ProofID = Proof_Fax.ProofID
IsCompleted = True AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE()));

Brendt Hess

A WITH statement can also do what you need. Note that an INNER JOIN is more efficient than an IN statement. Whether using a temp table or a WITH statement, use an INNER JOIN instead of an IN (SELECT...) statement

SET ANSI NULLS ON;
SET QUOTED_IDENTIFIER ON;

CREATE PROCEDURE PurgeCompletedCVLs
AS

;WITH proofIDs as (
SELECT ProofID
FROM Proof_Fax
WHERE IsCompleted = 1 -- 'True' is not valid - could be -1 or 1 depending on source
-- language. Check data for actual 'true' value
AND LastDeliveryDate <= DATEADD(DAY, -3, GETDATE()
)
BEGIN
BEGIN TRANSACTION
DELETE FROM Proof_Submitter
FROM Proof_Submitter ps
INNER JOIN proofIDs
ON ps.ProofID = proofIDs.ProofId
IF @@ERROR <> 0
BEGIN
RAISERROR (‘DELETE FROM Proof_Submitter ERROR’, 16,1)
GOTO _ROLLBACK
END

DELETE FROM Proof_Vehicles
FROM Proof_Vehicles pv
INNER JOIN proofIDs
ON pv.FormID = proofIDs.ProofId
IF @@ERROR <> 0
BEGIN
RAISERROR (‘DELETE FROM Proof_Vehicles ERROR’, 16,1)
GOTO _ROLLBACK
END

DELETE FROM Proof_Policy
FROM Proof_Policy pp
INNER JOIN proofIDs
ON pp.PolicyID = proofIDs.ProofId
IF @@ERROR <> 0
BEGIN
RAISERROR (‘DELETE FROM Proof_Policy ERROR’, 16,1)
GOTO _ROLLBACK
END

DELETE FROM Proof_Fax
FROM Proof_Fax pf
INNER JOIN proofIDs
ON pf.ProofID = proofIDs.ProofId
IF @@ERROR <> 0
BEGIN
RAISERROR (‘DELETE FROM Proof_Submitter ERROR’, 16,1)
GOTO _ROLLBACK
END
END
-- _COMMIT: -- don't use unreferenced labels
IF @@TRANCOUNT <> 0
COMMIT
RETURN 0 --GOTO _DONE

_ROLLBACK:
IF @@TRANCOUNT <> 0
ROLLBACK

Aneesh

>A WITH statement can also do what you need.
its not going to work here, the CTEs cannot live beyond the next statement
>Note that an INNER JOIN is more efficient than an IN statement.
It depends, for smaller inner recordset, IN is faster

dyarosh

ASKER

Thank you. That is what I was looking for.