Home
프로그램에 대한 생각
[Qmail Ubuntu기준 설치 및 운영]
[CentOS 5.X 기준] Qmail 설치 netqmail-1.05
windows 서버보안 방법

AuctionPro

옥션프로

Comments Posts

블로그롤

C# API MFC
C# MVC
CentsOS 정복
daily
eXtensible Stylesheet
Java 정복
Qmail 정복
SECURITY
Security COM
uAPI

msslq 프로시저) 오래된 데이타 자동 삭제


-- =============================================
-- 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

-- =============================================

-- Author: Author

-- Create date: 2018-03-28

-- Description: 오래된 데이타 자동 삭제

-- =============================================

CREATE PROCEDURE [dbo].[ClearDataSchedule]

-- Add the parameters for the stored procedure here

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

8월 29th, 2018 | Category: [DB]MSSQL Server | Leave a comment

저장 프로시저 기준 날짜 세팅


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

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

3월 28th, 2018 | Category: [DB]MSSQL Server | Leave a comment

mssql 저장프로시저 stuff 사용


-- ************ 처리 
set @lenCARDNUMBER  =  Len(@CARDNUMBER)
		   			   
   if @lenCARDNUMBER>4 
   Begin 
     Set @CARDNUMBER  = stuff(@CARDNUMBER, 1, @lenCARDNUMBER-4 , replicate('*',@lenCARDNUMBER-4))
     Set @CARDVALIDATE  = '**/**'
    end 

-- 출력 : ************6666

-- ************ 처리

set @lenCARDNUMBER = Len(@CARDNUMBER)

if @lenCARDNUMBER>4

Begin

Set @CARDNUMBER = stuff(@CARDNUMBER, 1, @lenCARDNUMBER-4 , replicate('*',@lenCARDNUMBER-4))

Set @CARDVALIDATE = '**/**'

end

-- 출력 : ************6666

2월 14th, 2018 | Category: [DB]MSSQL Server | Leave a comment

Base64Decode Base64Encode ms sql 스칼라 함수 작성


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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- 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


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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- 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

Select dbo.Base64Encode('123232') as number

//결과

MTIzMjMy

Select dbo.Base64Decode('MTIzMjMy') as number

//결과

123232

1월 30th, 2018 | Category: [DB]MSSQL Server | Leave a comment

msslq sp_attach_db


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'

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'

2월 26th, 2017 | Category: [DB]MSSQL Server | Leave a comment

« Newer Entries

Older Entries »

AuctionPro

카테고리

블로그롤

msslq 프로시저) 오래된 데이타 자동 삭제

저장 프로시저 기준 날짜 세팅

mssql 저장프로시저 stuff 사용

Base64Decode Base64Encode ms sql 스칼라 함수 작성

msslq sp_attach_db

최신 글