How to delete records selected from another table in stored procedure

1

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 is below.

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

Thanks

table
table547×533 7.72 KB

indent preformatted text by 4 spaces

2

I think this is what you are looking for

DELETE e 
from [dbo].[EmpMainTable] e
   join EmployLog l
      on l.setid = e.setid
   and e.created < DATEADD(dd, -@days, GETDATE());
1 Like
3

Personally I'd make sure you only deleted whole days rather than allowing the time the DELETE happened to run to allow a partial day to remain. If that means you need to back up another day, just add that to the -@days calc.

... e.created > DATEADD(DAY, -@days, CAST(GETDATE() AS date))