كود بلغة HTML:
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_UniqueRanking_UPDATE] by Caipi // Script Date: 03/06/2012 12:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_UniqueRanking_UPDATE]
@Path varchar(max), @Days int
as
SET NOCOUNT ON
Declare
@End varchar(max), @Date varchar(max),
@Start varchar(max), @1 varchar(max), @2 varchar(max), @3 varchar(max), @SEnd varchar(max);
SET @Start = (SELECT CONVERT(varchar(8), (GETDATE()-(1+(@Days))), 112))
SET @1 = SUBSTRING(@Start,1,4)
SET @2 = SUBSTRING(@Start,5,2)
SET @3 = SUBSTRING(@Start,7,2)
SET @SEnd = @1 + '-' + @2 + '-' + @3
SET @Date = @SEnd
SET @End = 'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_FatalLog.txt"'
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_finalkilllog')
BEGIN DROP TABLE #temp_finalkilllog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_fatallog')
BEGIN DROP TABLE #temp_fatallog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_killlog')
BEGIN DROP TABLE #temp_killlog END
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_FatalLog_Files')
BEGIN
CREATE TABLE _FatalLog_Files
(
[Already added] varchar(max) NULL
)
END
/*##*/
IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
BEGIN
/*#*/
CREATE TABLE #temp_fatallog (
[Date1] varchar(max),
[Date2] varchar(max),
[Text1] varchar(max)
)
EXEC (@End) /*BULK INSERT*/
DELETE FROM #temp_fatallog WHERE (Text1 not like '%Unique%' OR Text1 not like '%killed%')
SELECT Date1+' '+Date2 as 'Datum', Text1 as 'Textstring' INTO #temp_killlog FROM #temp_fatallog WITH (NOLOCK) ORDER BY Date2 asc
DROP TABLE #temp_fatallog
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_temp_UniqueRanking')
BEGIN
SELECT
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
INTO _temp_UniqueRanking
FROM #temp_killlog WITH (NOLOCK)
END
ELSE BEGIN
INSERT INTO _temp_UniqueRanking
SELECT
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
FROM #temp_killlog WITH (NOLOCK)
END
DROP TABLE #temp_killlog
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_UniqueRanking')
BEGIN
SELECT temp.[Killer],
temp.[Unique],
COUNT(temp.[Unique]) as Kills
INTO _UniqueRanking
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc
INSERT INTO _FatalLog_Files VALUES(@Date)
END
ELSE BEGIN
TRUNCATE TABLE _UniqueRanking
INSERT INTO _UniqueRanking
SELECT temp.[Killer],
temp.[Unique],
COUNT(temp.[Unique]) as Kills
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc
INSERT INTO _FatalLog_Files VALUES(@Date)
END
/*#*/
END
/*##*/
SET NOCOUNT OFF