• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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:
0
dyarosh
Asked:
dyarosh
1 Solution
 
Aneesh RetnakaranDatabase 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now