|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
-- ============================================= -- Author: Author -- Create date: 2018-03-28 -- Description: 오래된 데이타 자동 삭제 -- ============================================= CREATE PROCEDURE [dbo].[ClearDataSchedule] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Declare @OldDate DateTime Declare @sOldDate varchar(10) Set @OldDate=(DATEADD(mm,-5,GETDATE())) -- 5달 이전꺼 삭제 Set @sOldDate = CONVERT(CHAR(10),@OldDate, 23) -- Insert statements for procedure here delete FROM [MYDB].[dbo].[MYTABLE1] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE2] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE3] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE4] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE5] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE6] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE7] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE8] where [Regi_Date]<@sOldDate delete FROM [MYDB].[dbo].[MYTABLE9] where [Regi_Date]<@sOldDate End |