[RELEASE] Find Item by Serial

**Syloxx** · 22-08-14

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 @serial 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 @serial = 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 = @serial
		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 = @serial
		END




		--###############################################
		--#####       CHECK IF ITEM IS IN PET       #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
		BEGIN
			DECLARE @cosserial BIGINT,
				 @CoSItemID INT,
				 @CoSCodeName 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 = @serial
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			SELECT @cosserial = COSIT.Serial64,
				 @CoSItemID = COSIT.ID64,
				 @CoSCodeName = 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 = @serial
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			--##############################################
			--#####    CHECK IF PET IS IN INVENTORY    #####
			--##############################################
			IF  @CoSItemID IN (SELECT ItemID from _Inventory))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 @CoSCodeName 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 = @cosserial
			END




			--###############################################
			--#####     CHECK IF ITEM IS IN STORAGE     #####
			--###############################################
			ELSE IF  @CoSItemID IN (SELECT ItemID FROM _Chest))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 @CoSCodeName 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 = @cosserial
			END




			--##############################################
			--#####  CHECK IF PET IS IN GUILD STORAGE  #####
			--##############################################
			ELSE IF  @CoSItemID 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',
					 @CoSCodeName 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 = @cosserial
				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 = @serial
			AND GM.MemberClass = 0
		END

Procedure:

Code:

USE SRO_VT_SHARD
GO


CREATE PROCEDURE _FindItemBySerial
	 @serial 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 @serial = 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 = @serial
		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 = @serial
		END




		--###############################################
		--#####       CHECK IF ITEM IS IN PET       #####
		--###############################################
		ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
		BEGIN
			DECLARE @cosserial BIGINT,
				 @CoSItemID INT,
				 @CoSCodeName 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 = @serial
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			SELECT @cosserial = COSIT.Serial64,
				 @CoSItemID = COSIT.ID64,
				 @CoSCodeName = 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 = @serial
			AND ROC.TypeID2=2
			AND ROC.TypeID3=1
			AND ROC.TypeID4=2




			--##############################################
			--#####    CHECK IF PET IS IN INVENTORY    #####
			--##############################################
			IF  @CoSItemID IN (SELECT ItemID from _Inventory))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 @CoSCodeName 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 = @cosserial
			END




			--###############################################
			--#####     CHECK IF ITEM IS IN STORAGE     #####
			--###############################################
			ELSE IF  @CoSItemID IN (SELECT ItemID FROM _Chest))
			BEGIN
				SELECT	TBU.JID AS 'UserJID',
						TBU.StrUserID AS 'UserName',
						C.CharID,
						C.CharName16 AS 'CharName',
					 @CoSCodeName 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 = @cosserial
			END




			--##############################################
			--#####  CHECK IF PET IS IN GUILD STORAGE  #####
			--##############################################
			ELSE IF  @CoSItemID 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',
					 @CoSCodeName 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 = @cosserial
				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 = @serial
			AND GM.MemberClass = 0
		END

Execute the procedure:

Code:

USE SRO_VT_SHARD
GO


exec _FindItemBySerial YOUR_SERIAL_HERE

Inventory:

Storage:

COS Inventory:

Guild Storage:

**MentaL**

**POWEREDSHELL** · 22-08-14

what ?

**thebigbody** · 22-08-14

really thanks
and i hope the items in pet works fine

**Syloxx** · 22-08-14

Updated Pet!

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

**Neliel Tu** · 22-08-14

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** · 22-08-14

Originally Posted by CrystalCoder

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** · 22-08-14

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** · 22-08-14

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** · 23-08-14

Originally Posted by Syloxx

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** · 23-08-14

Originally Posted by CrystalCoder

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?

[RELEASE] Find Item by Serial

Thread Tools

[RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Re: [RELEASE] Find Item by Serial

Advertisement