[SERVICE] SQL Queries / Procedures

[Syloxx]

Hello Elitepvpers Community,

because many administrators are not experience enough to develop in SQL and don't wanna ask every 2nd guy to write a simple query i´ll open this thread.

In this thread a client can write some ideas with so many information's as possible and a voluntary develop the procedure / query for it then.

for sure i´ll develop also simple queries.

if u want some complex queries / procedures i can create them as well for a payment

[Media.]

[Syloxx]

Originally Posted by Media.

i guess u don't get the function of this thread ?

a guy can post a idea and if someone wanna develop it then he do it

[Media.]

I guess you don't get the function of the mentioned thread I posted. In fact it's exactly the same.

[Syloxx]

Originally Posted by Media.

I guess you don't get the function of the mentioned thread I posted. In fact it's exactly the same.

u can request for php developer or sql developer etc but not individual queries now plz dont fill the thread already with ur spam

[Media.]

Oh, so you mean someone can leave a request, and another one can offer his help?
Just like ?

Looking at the other thread it actually says

Which is for ALL requests, and of course to avoid a thread you just created.

[Syloxx]

Originally Posted by Media.

Oh, so you mean someone can leave a request, and another one can offer his help?
Just like ?

Looking at the other thread it actually says

Which is for ALL requests, and of course to avoid a thread you just created.

Request People???
i guess u killed your self with that post
the function of this thread is simply

find a team or request ppl for the team so there is no topic like:
'HEY MENZ MEN LOOKING FOR GEMAMESTAR'

and how i said stop spam a mod will choose whether it belongs in there or not

[Media.]

Originally Posted by Syloxx

Request People???

i guess u killed your self with that post

Are you aware that this is just a picture of the actual thread name?
If you spam the main section then you gotta expect spam in that thread.

[mane123]

Syloxx no news for your server ?!

[Ŧєคгlєรร]

Originally Posted by Media.

Are you aware that this is just a picture of the actual thread name?
If you spam the main section then you gotta expect spam in that thread.

Media... This topic is mainly for small releases aka. requests for the queries - not team member, so I don't see any issue, that this thread is useless.

Originally Posted by mane123

Syloxx no news for your server ?!

I think, that he has problem with ordering dedicated server as I have read on last posts.

@Thread:
Okay, so here's my really old query for cleaning vSRO database (2013, lol). It might be useful, if someone want to make his life easy and use just one line to clean whole database. Not sure is it working, just found that on my old disk, ah and warning - there might be small SQL Injection...

PHP Code:

-- Query Usage: exec _ClearDB DatabaseName, DatabaseType
-- Example: exec _ClearDB SRO_VT_ACCOUNT, ACC
-- Available options for DatabaseType: ACC - Account, SHR - Shard, LOG - Log
-- Supported: vSRO 188 
-- Author: Fearless, 3 Februrary 2013.

CREATE PROCEDURE [dbo].[_ClearDB]
@DBName varchar(32),
@DBType varchar(32)
AS

IF (@DBName != NULL or @DBType = NULL)
    print 'Wrong database name or settings!'
    else

IF @DBType = 'ACC'
BEGIN
    PRINT 'Detected database type: ACCOUNT'
    EXECUTE('USE ' + @DBName)
    PRINT 'Using' +@DBName+ ' as destination database'

    TRUNCATE TABLE [dbo].[__SiegeFortressStatus__]
    TRUNCATE TABLE [dbo].[_BlockedUser]
    TRUNCATE TABLE [dbo].[_CasGMChatLog]
    TRUNCATE TABLE [dbo].[_Notice]
    TRUNCATE TABLE [dbo].[_Punishment]
    TRUNCATE TABLE [dbo].[_ServiceManagerLog]
    TRUNCATE TABLE [dbo].[_ShardCurrentUser]
    TRUNCATE TABLE [dbo].[_SMCLog]
    TRUNCATE TABLE [dbo].[QuaySoEpoint]
    TRUNCATE TABLE [dbo].[SK_CharRenameLog]
    TRUNCATE TABLE [dbo].[SK_DownLevelLog]
    TRUNCATE TABLE [dbo].[SK_ITEM_GuardLog]
    TRUNCATE TABLE [dbo].[SK_ItemSaleLog]
    TRUNCATE TABLE [dbo].[SK_PackageItemSaleLog]
    TRUNCATE TABLE [dbo].[SK_PK_UpdateLog]
    TRUNCATE TABLE [dbo].[SK_ResetSkillLog]
    TRUNCATE TABLE [dbo].[SK_Silk]
    TRUNCATE TABLE [dbo].[SK_SilkBuyList]
    TRUNCATE TABLE [dbo].[SK_SubtractSilk_VAS]
    TRUNCATE TABLE [dbo].[SR_ShardCharNames]
    TRUNCATE TABLE [dbo].[TB_Net2e]
    TRUNCATE TABLE [dbo].[TB_Net2e_Bak]
    TRUNCATE TABLE [dbo].[tb_partnerInfo]
    TRUNCATE TABLE [dbo].[tb_paygate_trans]
    TRUNCATE TABLE [dbo].[TB_User]
    TRUNCATE TABLE [dbo].[TB_User_Bak]
    TRUNCATE TABLE [dbo].[Test_HN]
    PRINT +@DBName+ 'has been successfully cleaned!'
END

ELSE
    PRINT 'Wrong database type! Please check the query.'

IF @DBType = 'SHR'
BEGIN
    PRINT 'Detected database type: SHARD'
    EXECUTE('USE ' + @DBName)
    PRINT 'Using' +@DBName+ ' as destination database'

    TRUNCATE TABLE [dbo].[_Chest]
    TRUNCATE TABLE [dbo].[_ChestInfo]
    TRUNCATE TABLE [dbo].[_User]
    TRUNCATE TABLE [dbo].[_OpenMarket]
    DELETE FROM [dbo].[_AccountJID] 
    TRUNCATE TABLE [dbo].[_GuildWar]
    TRUNCATE TABLE [dbo].[_GuildMember]
    TRUNCATE TABLE [dbo].[_GuildChest]
    TRUNCATE TABLE [dbo].[_SiegeFortressStruct]
    TRUNCATE TABLE [dbo].[_SiegeFortressObject]
    TRUNCATE TABLE [dbo].[_SiegeFortressItemForge]
    DELETE FROM [dbo].[_SiegeFortress]
    INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (1)
    INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (3)
    INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (6)
    UPDATE [dbo].[_AlliedClans] SET Ally1 = '0', Ally2 = '0', Ally3 = '0', Ally4 = '0', Ally5 = '0', Ally6 = '0', Ally7 = '0', Ally8 = '0'
    DELETE FROM [dbo].[_Guild] WHERE ID > 0
    DELETE FROM [dbo].[_AlliedClans] WHERE ID > 0
    TRUNCATE TABLE [dbo].[_InventoryForAvatar]
    TRUNCATE TABLE [dbo].[_TrainingCampSubMentorHonorPoint]
    TRUNCATE TABLE [dbo].[_BlockedWhisperers]
    TRUNCATE TABLE [dbo].[_TrainingCampMember]
    TRUNCATE TABLE [dbo].[_CharTrijobSafeTrade]
    DELETE FROM [dbo].[_CharTrijob] WHERE CharID > 0
    TRUNCATE TABLE [dbo].[_TimedJob]
    TRUNCATE TABLE [dbo].[_StaticAvatar]
    TRUNCATE TABLE [dbo].[_Inventory]
    TRUNCATE TABLE [dbo].[_Memo]
    TRUNCATE TABLE [dbo].[_FleaMarketNetwork]
    TRUNCATE TABLE [dbo].[_Friend]
    TRUNCATE TABLE [dbo].[_CharSkillMastery]
    TRUNCATE TABLE [dbo].[_CharSkill]
    TRUNCATE TABLE [dbo].[_InvCOS]
    DELETE FROM [dbo].[_CharCOS] WHERE ID > 0
    DELETE FROM [dbo].[_Char] WHERE CharID > 0
    TRUNCATE TABLE [dbo].[_CharCollectionBook]
    TRUNCATE TABLE [dbo].[_CharInstanceWorldData]
    TRUNCATE TABLE [dbo].[_CharNameList]
    TRUNCATE TABLE [dbo].[_CharNickNameList]
    TRUNCATE TABLE [dbo].[_CharQuest]
    TRUNCATE TABLE [dbo].[_ClientConfig]
    TRUNCATE TABLE [dbo].[_DeletedChar]
    TRUNCATE TABLE [dbo].[_GPHistory]
    TRUNCATE TABLE [dbo].[_InventoryForLinkedStorage]
    TRUNCATE TABLE [dbo].[_ItemPool]
    DELETE FROM [dbo].[_Items] WHERE ID64 > 0
    TRUNCATE TABLE [dbo].[_Skill_BaoHiem_TNET]
    TRUNCATE TABLE [dbo].[_TimedJobForPet]
    TRUNCATE TABLE [dbo].[_TrainingCampBuffStatus]
    TRUNCATE TABLE [dbo].[_TrainingCampHonorRank]
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (1)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (2)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (3)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (4)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (5)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (6)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (7)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (8)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (9)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (10)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (11)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (12)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (13)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (14)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (15)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (16)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (17)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (18)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (19)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (20)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (21)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (22)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (23)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (24)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (25)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (26)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (27)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (28)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (29)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (30)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (31)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (32)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (33)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (34)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (35)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (36)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (37)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (38)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (39)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (40)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (41)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (42)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (43)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (44)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (45)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (46)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (47)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (48)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (49)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (50)
    EXEC [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
    DELETE FROM [dbo].[_TrainingCamp]
    TRUNCATE TABLE [dbo].[Tab_RefRanking_HunterContribution]
    TRUNCATE TABLE [dbo].[Tab_RefRanking_RobberContribution]
    TRUNCATE TABLE [dbo].[Tab_RefRanking_TraderContribution]
END

ELSE
    PRINT 'Wrong database type! Please check the query.'

IF @DBType = 'LOG'
BEGIN
    PRINT 'Detected database type: LOG'
    EXECUTE('USE ' + @DBName)
    PRINT 'Using' +@DBName+ ' as destination database'

    TRUNCATE TABLE [dbo].[_LogCashItem]
    TRUNCATE TABLE [dbo].[_LogEventChar]
    TRUNCATE TABLE [dbo].[_LogEventItem]
    TRUNCATE TABLE [dbo].[_LogEventSiegeFortress]
    TRUNCATE TABLE [dbo].[_LogSchedule]
    TRUNCATE TABLE [dbo].[_LogServerEvent]
    PRINT +@DBName+ 'has been successfully cleaned!'
END

ELSE
    PRINT 'Wrong database type! Please check the query.' 


P.S. The queries are from rz, I have just glued them >_>

[Syloxx]

here is a ban user by charname query if needen

CREATE PROCEDURE _BanUserByCharname
	@Charname varchar(16),
	@BanReason varchar(1024),
	@BanStartTime datetime,
	@BanEndTime datetime
AS

	DECLARE @JID INT,
			@StrUserID varchar(16),
			@Serial INT,
			@CharID INT
	
	SELECT @CharID = CharID FROM SRO_CH_SHARD.._Char WHERE CharName16=@Charname
	SELECT @JID = UserJID FROM SRO_CH_SHARD.._User WHERE CharID=@CharID
	SELECT @StrUserID = StrUserID FROM TB_User WHERE JID=@JID
	
	--Check is user exists
	IF (@CharID = 0 OR @CharID IS NULL)
	BEGIN
		PRINT 'Charname not exists!'
		RETURN
	END
					
	--Check if user is already blocked
	IF( EXISTS( SELECT UserJID FROM _BlockedUser WITH(NOLOCK) WHERE UserJID = @JID AND timeBegin < GETDATE()))
	BEGIN
		PRINT 'User already banned!'
		RETURN
	END
	
	INSERT INTO _Punishment VALUES (@JID , 1 , 'Database' , 64 , @Charname , @CharID , 0 , 'Database' , @BanReason , @BanStartTime , @BanStartTime , @BanEndTime , @BanEndTime , 1)

	--Generate Serial
	SET @Serial = @@IDENTITY
	IF( @@ROWCOUNT = 0 or @@ERROR <> 0 or @Serial = 0 or @Serial is null)
	BEGIN
		PRINT 'Generate Serial failed!'
		RETURN
	END
	
	INSERT INTO _BlockedUser VALUES (@JID , @StrUserID , 1 , @Serial , @BanStartTime , @BanEndTime)

[Media.]

Originally Posted by Ŧєคгlєรร

Media... This topic is mainly for small releases aka. requests for the queries - not team member, so I don't see any issue, that this thread is useless.

If this has the sole purpose of a query collection there's already a thread like that
Either way this belong to the release section then.

[Ŧєคгlєรร]

Originally Posted by Media.

If this has the sole purpose of a query collection there's already a thread like that
Either way this belong to the release section then.

Can you see requests in khaledlollol's thread? Because me not.