|
|
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 |
|
|
Declare @OldDate DateTime Declare @sOldDate varchar(10) Set @OldDate=(DATEADD(mm,-12,GETDATE())) --12달 전꺼 삭제 set @sOldDate = CONVERT(CHAR(10),@OldDate, 23) Select @sOldDate as DT -- 결과 2017-03-28 |
|
|
-- ************ 처리 set @lenCARDNUMBER = Len(@CARDNUMBER) if @lenCARDNUMBER>4 Begin Set @CARDNUMBER = stuff(@CARDNUMBER, 1, @lenCARDNUMBER-4 , replicate('*',@lenCARDNUMBER-4)) Set @CARDVALIDATE = '**/**' end -- 출력 : ************6666 |
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 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date, ,> -- Description: BASE64 디코딩 함수 -- ============================================= Create FUNCTION [dbo].[Base64Decode] ( @Encoded VARCHAR(max) ) RETURNS VARCHAR(MAX) BEGIN declare @Decoded VARCHAR(max) SELECT @Decoded = CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:column("bin"))', 'VARBINARY(MAX)') AS VARCHAR(MAX)) FROM ( SELECT CAST(@Encoded AS VARCHAR(MAX)) AS bin ) AS bin_sql_server_temp; RETURN @Decoded END |
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 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date, ,> -- Description: BASE64 인코딩 함수 -- ============================================= Create FUNCTION [dbo].[Base64Encode] ( @plain VARCHAR(max) ) RETURNS VARCHAR(MAX) BEGIN declare @encoded VARCHAR(max) SELECT @encoded = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)') FROM ( SELECT CAST(@plain AS VARBINARY(MAX)) AS bin ) AS bin_sql_server_temp; RETURN @encoded END |
사용
|
|
Select dbo.Base64Encode('123232') as number //결과 MTIzMjMy Select dbo.Base64Decode('MTIzMjMy') as number //결과 123232 |
|
|
Exec sp_attach_db 'MY_DB' ,'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MY_DB.mdf' ,'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MY_DB.LDF' |
|
|
