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:
dyaroshAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
CREATE PROCEDURE PurgeCompletedCVLs
AS
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

DECLARE @IDs TABLE (ProofID int ) 
INSERT INTO @IDs 
SELECT ProofID FROM Proof_Fax WHERE IsCompleted = True AND LastDeliveryDate <=  DATEADD(DAY, -3, GETDATE())
IF @@ROWCOUNT = 0 
 GOTO _DONE


BEGIN TRANSACTION
DELETE FROM Proof_Submitter WHERE ProofID IN (SELECT ProofID FROM @IDs)
      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 @IDs)
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 @IDs)
IF @@ERROR <> 0
      BEGIN
            RAISERROR ('DELETE FROM Proof_Policy ERROR', 16,1)
            GOTO _ROLLBACK
      END
DELETE FROM Proof_Fax WHERE Proof_ID IN (SELECT ProofID FROM @IDs)

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: 

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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())
0
 
Jared_SCommented:
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()));
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Brendt HessSenior DBACommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
0
 
dyaroshAuthor Commented:
Thank you.  That is what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.