0

I am trying to write a query which generates the Delete query automatically based on the requirements. I will get To be deleted data from different tables and dump into temp table and then using this temp table I need to write query using IN operator, please suggest me, this is what I have tried.

 SET NOCOUNT ON
    
    DECLARE @DeleteDisbursement TABLE (DisbursementId BIGINT, PolicyNumber NVARCHAR(10),DisbursementAmount DECIMAL(19,4))
    DECLARE @DeleteRecords Table(DisbursementId BIGINT,ActivityId BIGINT)
    INSERT INTO @DeleteDisbursement (DisbursementId,PolicyNumber,DisbursementAmount) 
    VALUES 
    (4576,'12345',3.00),
    (1232,'65455',143.44),
    (2341,'34234',228.95),
    (1111,'23155',414.89)
    
    SELECT * from @DeleteDisbursement
    
    DECLARE @GenerateScriptCount BIGINT =0
    DECLARE @DisbursementIDValue BIGINT =0
    DECLARE @ActivityIdValue BIGINT =0
    
    WHILE EXISTS(SELECT Top 1 1 FROM @DeleteDisbursement)
    BEGIN
       DECLARE @DisbursementId BIGINT =0
       DECLARE @PolicyNumber NVARCHAR(10)
       DECLARE @AccountId BIGINT =0
       DECLARE @ActivityId BIGINT
       DECLARE @DisbursementAmount DECIMAL(19,4)
    
    SELECT TOP 1 @DisbursementId =DisbursementId ,@PolicyNumber = PolicyNumber,@DisbursementAmount = DisbursementAmount
      FROM @DeleteDisbursement
    
    SET @ActivityId = (SELECT ActivityId FROM ActivityLog WHERE ActivityAmount = @DisbursementAmount)  
      
      IF (@ActivityId > 0)
        BEGIN
          
         INSERT INTO @DeleteRecords (DisbursementId,ActivityId) VALUES(@DisbursementId,@ActivityId)
        
        END
      DELETE FROM @DeleteDisbursement WHERE DisbursementId = @DisbursementId
    
    END
        
        WHILE EXISTS(SELECT Top 1 1 FROM @DeleteRecords)
        BEGIN
    
            SELECT TOP 1 @DisbursementIDValue= @DisbursementId,@ActivityIdValue = @ActivityId FROM @DeleteRecords 
    
             PRINT 'DELETE FROM Disbursement Where DisbursementId IN (' + CONVERT(NVARCHAR(MAX), @DisbursementIDValue) +')' 
    
             PRINT 'DELETE FROM ActivityLog WHERE ActivityId IN (' + CONVERT(NVARCHAR(MAX), @ActivityIdValue) +')'  
    
            DELETE FROM @DeleteRecords WHERE DisbursementId = @DisbursementId
    
        END
    
    SET NOCOUNT OFF;

But its not working, Please help me How do I write query only 1 query which included in IN Operator like below :- Dynamically I want to PRINT this as I have showed in my query

 DELETE FROM Disbursement Where DisbursementId IN (4576,1232,2341,1111)
    DELETE FROM ActivityLog WHERE ActivityId IN (1000,2000,3000,4000)
user613400
  • 53
  • 5
  • No you don't. You want to join to the temp table in your delete statement. In fact you can probably replace all your code with a couple delete statements. – Jeremy Lakeman Nov 03 '20 at 00:17
  • so you mean I will not be able to achieve this -> DELETE FROM Disbursement Where DisbursementId IN (4576,1232,2341,1111) – user613400 Nov 03 '20 at 00:26
  • Please suggest me If there are any other way I can able to do that – user613400 Nov 03 '20 at 00:26
  • But why? Step back and explain exactly what the purpose of your script is (it's a little hard to follow and reverse engineer what your objective is). – Jeremy Lakeman Nov 03 '20 at 00:28
  • I think you're trying to use one set of data to delete from other sets of data. If so, just do that with a single `delete from xxx join yyy ...` (https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server) – Jeremy Lakeman Nov 03 '20 at 00:30
  • I am trying to achieve is - I have created Temp table, in that table I have inserted all the records which I want to delete. Using this temp table now I have to Generate Delete query with IN operator, this is basically for Performance purpose, I can able to achieve using = , but its taking very long time in Prod, so I have to achieve using only IN Operator So my query will just do the Print operation so manually I will copy the Print Generated qiuery and will place to Production for DELETION purpose – user613400 Nov 03 '20 at 00:34
  • input to temp table might be 1000 or 2000 values, that's why I have to use IN operator, I hope I am clear now – user613400 Nov 03 '20 at 00:35
  • Don't comment with more details, explain in your question. " now I have to Generate Delete query with IN operator", no you don't. This is a classic XY problem. What you want is a delete that joins to your temp table to remove everything at once. – Jeremy Lakeman Nov 03 '20 at 00:36

0 Answers0