inactive characters remover procedure

[DrugDealers]

well after i saw some people request this function i decided to help
as topic says this procedure to remove inactive characters with your specified days


proc

Spoiler

CREATE PROC _InActive_Character_Remover days INT, minLevel tinyint, MaxLevel tinyint
AS
SET XACT_ABORT ON
SET NOCOUNT ON
/*
simple inactive character remove
version 1.1
all credit goes to king dollar
*/


--DECLARING SHITS
DECLARE CharID INT
DECLARE Charname varchar(64)








BEGIN TRY
BEGIN TRANSACTION
DECLARE CharShit CURSOR LOCAL FOR SELECT CharID,CharName16 FROM SRO_VT_SHARD.._Char WHERE CONVERT(VARCHAR(30),DATEDIFF(DAY,GETDATE(),LastLogout)) >= '-'+CONVERT(VARCHAR(30) days) AND CharName16 != 'd' and CurLevel between minLevel and MaxLevel
OPEN CharShit;
FETCH NEXT FROM CharShit INTO CharID Charname;
WHILE @@FETCH_STATUS = 0
BEGIN


print 'Deleted ' + Charname + ' Reason : because it wasn''t active for more than '+CONVERT(VARCHAR(30) days) +' days and level between '+CONVERT(VARCHAR(30) minLevel) +' and '+CONVERT(VARCHAR(30) MaxLevel)
DELETE FROM SRO_VT_SHARD.._User WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._GuildMember WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._GPHistory WHERE CharName = Charname
DELETE FROM SRO_VT_SHARD.._TrainingCampMember WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._TrainingCamp WHERE ID IN(SELECT CampID FROM _TrainingCampMember WHERE CharID = CharID)
DELETE FROM SRO_VT_SHARD.._TrainingCampBuffStatus WHERE CampID IN(SELECT CampID FROM _TrainingCampMember WHERE CharID = CharID)
DELETE FROM SRO_VT_SHARD.._TrainingCampHonorRank WHERE CampID IN(SELECT CampID FROM _TrainingCampMember WHERE CharID = CharID)
DELETE FROM SRO_VT_SHARD.._TrainingCampSubMentorHonorPoint WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharNameList WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharNickNameList WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._BlackNameList WHERE BlacklistName = Charname
DELETE FROM SRO_VT_SHARD.._BlockedWhisperers WHERE OwnerID = CharID OR TargetName = Charname
DELETE FROM SRO_VT_SHARD.._CharCollectionBook WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharInstanceWorldData WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharQuest WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharSkill WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharSkillMastery WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharTrijobSafeTrade WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._CharTrijob WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._TimedJob WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._DeletedChar WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._ClientConfig WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._TimedJobForPet WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._TimedJob WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._FleaMarketNetwork WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._Friend WHERE CharID = CharID OR FriendCharName = Charname
DELETE FROM SRO_VT_SHARD.._BindingOptionWithItem WHERE nItemDBID IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar WHERE CharID = CharID) OR nItemDBID IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory WHERE CharID = CharID) OR nItemDBID IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS WHERE COSID IN (SELECT ID FROM SRO_VT_SHARD.._CharCOS WHERE OwnerCharID = CharID OR OwnerCharID = 0))
DELETE FROM SRO_VT_SHARD.._InvCOS WHERE COSID IN (SELECT ID FROM _CharCOS WHERE OwnerCharID = CharID OR OwnerCharID = 0)
DELETE FROM SRO_VT_SHARD.._Inventory WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._InventoryForAvatar WHERE CharID = CharID
UPDATE SRO_VT_SHARD.._Chest SET ItemID = 0 WHERE ITEMID IN(SELECT ID64 FROM SRO_VT_SHARD.._Items WHERE ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ID64 > 0)
UPDATE SRO_VT_SHARD.._OpenMarket SET ItemID = 0 WHERE ITEMID NOT IN(SELECT ID64 FROM SRO_VT_SHARD.._Items WHERE ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ID64 > 0)
DELETE FROM SRO_VT_SHARD.._ItemPool WHERE ItemID NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ItemID NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ItemID NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ItemID > 0
DELETE FROM SRO_VT_SHARD.._Items WHERE ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ID64 > 0
DELETE FROM SRO_VT_SHARD.._OpenMarket WHERE CharName16 = Charname and ITEMID = 0
DELETE FROM SRO_VT_SHARD.._Memo WHERE CharID = CharID OR FromCharName = Charname
DELETE FROM SRO_VT_SHARD.._CharCOS WHERE OwnerCharID = CharID
DELETE FROM SRO_VT_SHARD.._StaticAvatar WHERE CharID = CharID
DELETE FROM SRO_VT_SHARD.._Char WHERE CharID = CharID
FETCH NEXT FROM CharShit INTO CharID Charname;
END;
CLOSE CharShit;
DEALLOCATE CharShit;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
ROLLBACK TRANSACTION
END
END CATCH
GO

usage

Spoiler

exec _InActive_Character_Remover '30' /*days that you want at least be characters inactive time*/,'1' /*min level that you want at least be characters inactive level */ ,'50' /*max level that you want at least be characters inactive level*/

greeting

 

[DrugDealers]

--updated version 1.1 changelog
added min level that you want at least be characters inactive level
added maxlevel that you want at least be characters inactive level
fixed some errors on code

 

[Rango]

hi

Error Message : The DELETE statement conflicted with the REFERENCE constraint "FK__InventoryForLinkedStorage__Items". The conflict occurred in database "SRO_VT_SHARD", table "dbo._InventoryForLinkedStorage", column 'ItemID'.

 

[a22345875]

Prompt success, but invalid