[SQL, PERFORMANCE, Proc]_Roya_AddItems_Fast

CREATE PROCEDURE [dbo].[_RoyalAddItems]
@CharID INT,
@ItemID INT,
@Amount INT,
@OptLvl INT
AS
SET XACT_ABORT ON
DECLARE	@FreeSlot TINYINT,
		@Link INT,
		@LatestItemSN BIGINT,
		@NewItemID BIGINT = 0;
 
SELECT	@Link = R.Link,
		@Amount =	CASE	
						WHEN TypeID1 = 3 AND TypeID2 = 1
							THEN Dur_L
						WHEN TypeID1 = 3 AND TypeID2 = 2 AND TypeID3 = 1 AND TypeID4 IN(1,2)
							THEN 0
						WHEN @Amount > RI.MaxStack OR @Amount < 0
							THEN MaxStack
						ELSE @Amount
					END
FROM	_RefObjCommon R
JOIN	_RefObjItem RI	ON R.Link = RI.ID
WHERE	R.ID = @ItemID

IF @Link IS NULL OR @Amount IS NULL
		RETURN -4 -- Self-explanatory

	
IF NOT EXISTS(SELECT * FROM _Char WITH (NOLOCK) WHERE CharID = @CharID)
		RETURN -3 -- Character doesnt exist.

SELECT TOP 1 @FreeSlot = Slot
FROM	_Inventory
WHERE	CharID = @CharID
AND		Slot > 12
AND		ItemID = 0

IF	@FreeSlot IS NULL
		RETURN -2 -- Inventory is full.


BEGIN TRAN
BEGIN TRY

UPDATE _LatestItemSerial SET LatestItemSerial += 1;

SELECT @LatestItemSN = LatestItemSerial 
FROM _LatestItemSerial 
WITH (UPDLOCK)

INSERT INTO _Items (RefItemID, OptLevel, Data, MagParamNum, Serial64) 
VALUES (@ItemID, @OptLvl, @Amount, 0, @LatestItemSN)
SELECT	@NewItemID = @@IDENTITY

UPDATE _Inventory SET ItemID = @NewItemID WHERE CharID = @CharID AND Slot = @FreeSlot

INSERT INTO _ItemPool (InUse, ItemID)
VALUES		(1,@NewItemID)
		
IF	@NewItemID = 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
END TRY
BEGIN CATCH -- You can remove the try n catch block. This should always work.. I just wanted to be sure.
	ROLLBACK TRAN
	RETURN 
END CATCH
	
COMMIT TRAN