How to delete records selected from another table in stored procedure

• John_Kennedy

  Old Hand

  Points: 330

  More actions

  January 24, 2022 at 1:48 pm

  Go to Answer

  #3976523

  CREATE PROCEDURE usp_DeleteEmployeeRcord
  (
   @days Int
  )
  AS 
          BEGIN
              DELETE [dbo].[EmpMainTable]  
              Where created < DATEADD(dd, -@days, GETDATE());
          END
  

  Hello Everyone,

  Above is my stored Procedure. I need to delete records in 'EmployLog' table selected from 'EmpMainTable'. here i need to delete older data more than 1 month (@days parameter).

  How i need to select old records from one table and delete from another table in a single stored procedure.

  I have attached my table and stored procedure.

   

  Thanks

  Attachments:

  You must be logged in to view attached files.

• DesNorton

  SSC-Insane

  Points: 24529

  More actions

  January 24, 2022 at 2:09 pm

  #3976526

  You will have to do 2 separate deletes.  And then I would also look at adding error handling and transaction control.

  Also, depending on the size of the data, you may want to do the deletes in batches.

  BEGIN
    /*** First delete the log records ***/  DELETE lg
    FROM [dbo].[EmpMainTable] AS mt
    INNER JOIN [dbo].[EmpLoyLog] AS lg ON mt.[Setld] = lg.[Setld]
    WHERE mt.created < DATEADD(dd, -@days, GETDATE());
  
    /*** Then delete the main records ***/  DELETE mt
    FROM [dbo].[EmpMainTable] AS mt
    INNER JOIN [dbo].[EmpLoyLog] AS lg ON mt.[Setld] = lg.[Setld]
    WHERE mt.created < DATEADD(dd, -@days, GETDATE());
  END

  ---

  How to post data/code on a forum to get the best help.

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  January 24, 2022 at 3:46 pm

  Answer

  #3976576

  In SQL Server, you can use DELETE from a table using a JOIN.  Just be sure to alias the table you're deleting from and DELETE from the alias, not the original table name.  That is:

  DELETE FROM EMT

  FROM dbo.EmpMainTable EMT

  INNER JOIN ... ON ...

  [WHERE ...]

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".