[RELEASE] Find Item by Serial

Syloxx · Aug 22, 2014

I want to release something useful for all Silkroad administrators, so here is my "Find Item by Serial" Query / Stored Procedure.

Query:

Code:

USE SRO_VT_SHARD
GO


--//////////FindItemBySerial V1.3 By Syloxx\\\\\\\\\
DECLARE [USER=445157]serial[/USER] BIGINT = YOUR_SERIAL_HERE,
		@ItemID INT,
		@ItemCodeName VARCHAR(128)


		SELECT	@ItemCodeName = C.CodeName128 , 
				@ItemID = I.ID64
		FROM _RefObjCommon C
		INNER JOIN _Items I
		ON C.ID = I.RefItemID
		WHERE [USER=445157]serial[/USER] = I.Serial64




		--###############################################
		--#####       CHECK IF SERIAL IS VALID      #####
		--###############################################
		IF @ItemID IS NULL OR @ItemID = 0
		BEGIN
			PRINT 'No item with the specified Serial found!'
		END




		--###############################################
		--#####    CHECK IF ITEM IS IN INVENTORY    #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID from _Inventory))
		BEGIN
			SELECT	TBU.JID AS 'UserJID',
					TBU.StrUserID AS 'UserName',
					C.CharID,
					C.CharName16 AS 'CharName',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'Inventory' AS 'Storage',
					INV.Slot
			FROM _Items IT
			INNER JOIN _Inventory INV
			ON INV.ItemID = IT.ID64
			INNER JOIN _Char C
			ON C.CharID = INV.CharID
			INNER JOIN _User U
			ON U.CharID = C.CharID
			INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
			ON TBU.JID = U.UserJID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
		END




		--###############################################
		--#####     CHECK IF ITEM IS IN STORAGE     #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _Chest))
		BEGIN
			SELECT	TBU.JID AS 'UserJID',
					TBU.StrUserID AS 'UserName',
					C.CharID,
					C.CharName16 AS 'CharName',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'Storage' AS 'Storage',
					SINV.Slot
			FROM _Items IT
			INNER JOIN _Chest SINV
			ON SINV.ItemID = IT.ID64
			INNER JOIN _User U
			ON U.UserJID = SINV.UserJID
			INNER JOIN _Char C
			ON C.CharID = U.CharID
			INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
			ON TBU.JID = U.UserJID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
		END




		--###############################################
		--#####       CHECK IF ITEM IS IN PET       #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
		BEGIN
			DECLARE [USER=302806]coss[/USER]erial BIGINT,
				 [USER=1333422870]CoS[/USER]ItemID INT,
				 [USER=1333422870]CoS[/USER]CodeName VARCHAR(128)


			SELECT	CCOS.ID AS 'COSID',
					ROC.CodeName128 AS 'COS CodeName',
					CCOS.CharName AS 'COS Name',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'COS Inventory' AS 'Storage',
					CINV.Slot
			FROM _Items IT
			INNER JOIN _InvCOS CINV
			ON CINV.ItemID = IT.ID64
			INNER JOIN _CharCOS CCOS
			ON CCOS.ID = CINV.COSID
			INNER JOIN _Items COSIT
			ON COSIT.Data = CCOS.ID
			INNER JOIN _RefObjCommon ROC
			ON ROC.ID = COSIT.RefItemID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			SELECT [USER=302806]coss[/USER]erial = COSIT.Serial64,
				 [USER=1333422870]CoS[/USER]ItemID = COSIT.ID64,
				 [USER=1333422870]CoS[/USER]CodeName = ROC.CodeName128
			FROM _Items IT
			INNER JOIN _InvCOS CINV
			ON CINV.ItemID = IT.ID64
			INNER JOIN _CharCOS CCOS
			ON CCOS.ID = CINV.COSID
			INNER JOIN _Items COSIT
			ON COSIT.Data = CCOS.ID
			INNER JOIN _RefObjCommon ROC
			ON ROC.ID = COSIT.RefItemID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			--##############################################
			--#####    CHECK IF PET IS IN INVENTORY    #####
			--##############################################
			IF  [USER=1333422870]CoS[/USER]ItemID IN (SELECT ItemID from _Inventory))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 [USER=1333422870]CoS[/USER]CodeName AS 'COS CodeName',
						IT.OptLevel,
						'Inventory' AS 'Storage',
						INV.Slot
				FROM _Items IT
				INNER JOIN _Inventory INV
				ON INV.ItemID = IT.ID64
				INNER JOIN _Char C
				ON C.CharID = INV.CharID
				INNER JOIN _User U
				ON U.CharID = C.CharID
				INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
				ON TBU.JID = U.UserJID
				WHERE IT.Serial64 = [USER=302806]coss[/USER]erial
			END




			--###############################################
			--#####     CHECK IF ITEM IS IN STORAGE     #####
			--###############################################
			ELSE IF  [USER=1333422870]CoS[/USER]ItemID IN (SELECT ItemID FROM _Chest))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 [USER=1333422870]CoS[/USER]CodeName AS 'COS CodeName',
						IT.OptLevel,
						'Storage' AS 'Storage',
						SINV.Slot
				FROM _Items IT
				INNER JOIN _Chest SINV
				ON SINV.ItemID = IT.ID64
				INNER JOIN _User U
				ON U.UserJID = SINV.UserJID
				INNER JOIN _Char C
				ON C.CharID = U.CharID
				INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
				ON TBU.JID = U.UserJID
				WHERE IT.Serial64 = [USER=302806]coss[/USER]erial
			END




			--##############################################
			--#####  CHECK IF PET IS IN GUILD STORAGE  #####
			--##############################################
			ELSE IF  [USER=1333422870]CoS[/USER]ItemID IN (SELECT ItemID FROM _GuildChest))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						GM.CharID,
						GM.CharName AS 'GuildMaster',
						G.ID AS 'GuildID',
						G.Name AS 'GuildName',
					 [USER=1333422870]CoS[/USER]CodeName AS 'COS CodeName',
						IT.OptLevel,
						'Guild Storage' AS 'Storage',
						GINV.Slot
				FROM _Items IT
				INNER JOIN _GuildChest GINV
				ON GINV.ItemID = IT.ID64
				INNER JOIN _Guild G
				ON G.ID = GINV.GuildID
				INNER JOIN _GuildMember GM
				ON GM.GuildID = G.ID
				INNER JOIN _User U
				ON U.CharID = GM.CharID
				INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
				ON TBU.JID = U.UserJID
				WHERE IT.Serial64 = [USER=302806]coss[/USER]erial
				AND GM.MemberClass = 0
			END
		END




		--###############################################
		--#####  CHECK IF ITEM IS IN GUILD STORAGE  #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _GuildChest))
		BEGIN
			SELECT	TBU.JID AS 'UserJID',
					TBU.StrUserID AS 'UserName',
					GM.CharID,
					GM.CharName AS 'GuildMaster',
					G.ID AS 'GuildID',
					G.Name AS 'GuildName',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'Guild Storage' AS 'Storage',
					GINV.Slot
			FROM _Items IT
			INNER JOIN _GuildChest GINV
			ON GINV.ItemID = IT.ID64
			INNER JOIN _Guild G
			ON G.ID = GINV.GuildID
			INNER JOIN _GuildMember GM
			ON GM.GuildID = G.ID
			INNER JOIN _User U
			ON U.CharID = GM.CharID
			INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
			ON TBU.JID = U.UserJID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
			AND GM.MemberClass = 0
		END

Procedure:

Code:

USE SRO_VT_SHARD
GO


CREATE PROCEDURE _FindItemBySerial
	 [USER=445157]serial[/USER] BIGINT
AS
--//////////FindItemBySerial V1.3 By Syloxx\\\\\\\\\
DECLARE @ItemID INT,
		@ItemCodeName VARCHAR(128)


		SELECT	@ItemCodeName = C.CodeName128 , 
				@ItemID = I.ID64
		FROM _RefObjCommon C
		INNER JOIN _Items I
		ON C.ID = I.RefItemID
		WHERE [USER=445157]serial[/USER] = I.Serial64




		--###############################################
		--#####       CHECK IF SERIAL IS VALID      #####
		--###############################################
		IF @ItemID IS NULL OR @ItemID = 0
		BEGIN
			PRINT 'No item with the specified Serial found!'
		END




		--###############################################
		--#####    CHECK IF ITEM IS IN INVENTORY    #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID from _Inventory))
		BEGIN
			SELECT	TBU.JID AS 'UserJID',
					TBU.StrUserID AS 'UserName',
					C.CharID,
					C.CharName16 AS 'CharName',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'Inventory' AS 'Storage',
					INV.Slot
			FROM _Items IT
			INNER JOIN _Inventory INV
			ON INV.ItemID = IT.ID64
			INNER JOIN _Char C
			ON C.CharID = INV.CharID
			INNER JOIN _User U
			ON U.CharID = C.CharID
			INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
			ON TBU.JID = U.UserJID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
		END




		--###############################################
		--#####     CHECK IF ITEM IS IN STORAGE     #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _Chest))
		BEGIN
			SELECT	TBU.JID AS 'UserJID',
					TBU.StrUserID AS 'UserName',
					C.CharID,
					C.CharName16 AS 'CharName',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'Storage' AS 'Storage',
					SINV.Slot
			FROM _Items IT
			INNER JOIN _Chest SINV
			ON SINV.ItemID = IT.ID64
			INNER JOIN _User U
			ON U.UserJID = SINV.UserJID
			INNER JOIN _Char C
			ON C.CharID = U.CharID
			INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
			ON TBU.JID = U.UserJID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
		END




		--###############################################
		--#####       CHECK IF ITEM IS IN PET       #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
		BEGIN
			DECLARE [USER=302806]coss[/USER]erial BIGINT,
				 [USER=1333422870]CoS[/USER]ItemID INT,
				 [USER=1333422870]CoS[/USER]CodeName VARCHAR(128)


			SELECT	CCOS.ID AS 'COSID',
					ROC.CodeName128 AS 'COS CodeName',
					CCOS.CharName AS 'COS Name',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'COS Inventory' AS 'Storage',
					CINV.Slot
			FROM _Items IT
			INNER JOIN _InvCOS CINV
			ON CINV.ItemID = IT.ID64
			INNER JOIN _CharCOS CCOS
			ON CCOS.ID = CINV.COSID
			INNER JOIN _Items COSIT
			ON COSIT.Data = CCOS.ID
			INNER JOIN _RefObjCommon ROC
			ON ROC.ID = COSIT.RefItemID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			SELECT [USER=302806]coss[/USER]erial = COSIT.Serial64,
				 [USER=1333422870]CoS[/USER]ItemID = COSIT.ID64,
				 [USER=1333422870]CoS[/USER]CodeName = ROC.CodeName128
			FROM _Items IT
			INNER JOIN _InvCOS CINV
			ON CINV.ItemID = IT.ID64
			INNER JOIN _CharCOS CCOS
			ON CCOS.ID = CINV.COSID
			INNER JOIN _Items COSIT
			ON COSIT.Data = CCOS.ID
			INNER JOIN _RefObjCommon ROC
			ON ROC.ID = COSIT.RefItemID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			--##############################################
			--#####    CHECK IF PET IS IN INVENTORY    #####
			--##############################################
			IF  [USER=1333422870]CoS[/USER]ItemID IN (SELECT ItemID from _Inventory))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 [USER=1333422870]CoS[/USER]CodeName AS 'COS CodeName',
						IT.OptLevel,
						'Inventory' AS 'Storage',
						INV.Slot
				FROM _Items IT
				INNER JOIN _Inventory INV
				ON INV.ItemID = IT.ID64
				INNER JOIN _Char C
				ON C.CharID = INV.CharID
				INNER JOIN _User U
				ON U.CharID = C.CharID
				INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
				ON TBU.JID = U.UserJID
				WHERE IT.Serial64 = [USER=302806]coss[/USER]erial
			END




			--###############################################
			--#####     CHECK IF ITEM IS IN STORAGE     #####
			--###############################################
			ELSE IF  [USER=1333422870]CoS[/USER]ItemID IN (SELECT ItemID FROM _Chest))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 [USER=1333422870]CoS[/USER]CodeName AS 'COS CodeName',
						IT.OptLevel,
						'Storage' AS 'Storage',
						SINV.Slot
				FROM _Items IT
				INNER JOIN _Chest SINV
				ON SINV.ItemID = IT.ID64
				INNER JOIN _User U
				ON U.UserJID = SINV.UserJID
				INNER JOIN _Char C
				ON C.CharID = U.CharID
				INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
				ON TBU.JID = U.UserJID
				WHERE IT.Serial64 = [USER=302806]coss[/USER]erial
			END




			--##############################################
			--#####  CHECK IF PET IS IN GUILD STORAGE  #####
			--##############################################
			ELSE IF  [USER=1333422870]CoS[/USER]ItemID IN (SELECT ItemID FROM _GuildChest))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						GM.CharID,
						GM.CharName AS 'GuildMaster',
						G.ID AS 'GuildID',
						G.Name AS 'GuildName',
					 [USER=1333422870]CoS[/USER]CodeName AS 'COS CodeName',
						IT.OptLevel,
						'Guild Storage' AS 'Storage',
						GINV.Slot
				FROM _Items IT
				INNER JOIN _GuildChest GINV
				ON GINV.ItemID = IT.ID64
				INNER JOIN _Guild G
				ON G.ID = GINV.GuildID
				INNER JOIN _GuildMember GM
				ON GM.GuildID = G.ID
				INNER JOIN _User U
				ON U.CharID = GM.CharID
				INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
				ON TBU.JID = U.UserJID
				WHERE IT.Serial64 = [USER=302806]coss[/USER]erial
				AND GM.MemberClass = 0
			END
		END




		--###############################################
		--#####  CHECK IF ITEM IS IN GUILD STORAGE  #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _GuildChest))
		BEGIN
			SELECT	TBU.JID AS 'UserJID',
					TBU.StrUserID AS 'UserName',
					GM.CharID,
					GM.CharName AS 'GuildMaster',
					G.ID AS 'GuildID',
					G.Name AS 'GuildName',
					@ItemCodeName AS 'Item CodeName',
					IT.OptLevel,
					'Guild Storage' AS 'Storage',
					GINV.Slot
			FROM _Items IT
			INNER JOIN _GuildChest GINV
			ON GINV.ItemID = IT.ID64
			INNER JOIN _Guild G
			ON G.ID = GINV.GuildID
			INNER JOIN _GuildMember GM
			ON GM.GuildID = G.ID
			INNER JOIN _User U
			ON U.CharID = GM.CharID
			INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
			ON TBU.JID = U.UserJID
			WHERE IT.Serial64 = [USER=445157]serial[/USER]
			AND GM.MemberClass = 0
		END

Execute the procedure:

Code:

USE SRO_VT_SHARD
GO


exec _FindItemBySerial YOUR_SERIAL_HERE

Inventory:

Syloxx - [RELEASE] Find Item by Serial - RaGEZONE Forums

Storage:

COS Inventory:

Guild Storage:

POWEREDSHELL · Aug 22, 2014

what ?

thebigbody · Aug 22, 2014

really thanks
and i hope the items in pet works fine

:

Syloxx · Aug 22, 2014

Updated Pet!

Pet works now even if the pet is in Storage or Guild Storage

[GM]Xia · Aug 22, 2014

its for twsro files
so i found it usless since the files arent public
and who got them can only do it
so next them you post such usefull staff make sure its for files everyone using
otherwise dont post it

Syloxx · Aug 22, 2014

CrystalCoder said:
its for twsro files
so i found it usless since the files arent public
and who got them can only do it
so next them you post such usefull staff make sure its for files everyone using
otherwise dont post it

whats about testing be4 u write something like that

this query is working for ANY files

Caosfox · Aug 22, 2014

maybe works for any files, but ofc you need to edit it for that
not all dbs are named SRO_TW_SHARD, and maybe another small modifications

Syloxx · Aug 22, 2014

I think I can assume that everyone knows how to change a db name in a query

€:
V1.2 Changelog

-added missing "END"
-changed SHARD DB from sro_tw_shard to SRO_VT_SHARD
-changed ACCOUNTDB from sro_tw_accountdb to SRO_VT_ACCOUNT

V1.3 Changelog

-optimized query

Caosfox · Aug 22, 2014

Syloxx said:
I think I can assume that everyone knows how to change a db name in a query

believe me.. no .. they dont.... never... not in this life.

many.. many .. MANY are simply copy & paste "devs"...

POWEREDSHELL · Aug 23, 2014

CrystalCoder said:
its for twsro files
so i found it usless since the files arent public
and who got them can only do it
so next them you post such usefull staff make sure its for files everyone using
otherwise dont post it

CrystalCoder?

are you sure?

Welcome!

[RELEASE] Find Item by Serial

Syloxx

Newbie Spellweaver

POWEREDSHELL

Banned

thebigbody

Skilled Illusionist

Syloxx

Newbie Spellweaver

[GM]Xia

Blade & Soul Eldoria Developer

Syloxx

Newbie Spellweaver

Caosfox

In the Emperor name

Syloxx

Newbie Spellweaver

Caosfox

In the Emperor name

POWEREDSHELL

Banned

About Us

Online statistics

Forum statistics

RaGEZONE Sponsor