![[RELEASE] Usefull SQL statements - for GM's](https://www.elitepvpers.com/forum/iconimages/shaiya-pserver-development/release-usefull-sql-statements-gms_ltr.gif){kind=small}
To make it work type the monster name between the percent signs (red colour). It doesn't have to be the full name. Partial names will work too, i.e. '%Ruined Wolf%', '%Cryptic%':
Code:
DECLARE @MobName varchar(60);
SET @MobName = [COLOR=Red]'%%'[/COLOR];
SELECT M.MobName, I.ItemName, MI.Grade, MI.DropRate
FROM PS_GameDefs.dbo.Mobs AS M INNER JOIN PS_GameDefs.dbo.MobItems AS MI
ON M.MobID = MI.MobID AND MI.Grade<>0 LEFT OUTER JOIN PS_GameDefs.dbo.Items AS I
ON MI.Grade = I.Grade
WHERE M.MobName LIKE @MobName
ORDER BY I.Grade, I.ItemName;
SQL Version of Deleting Player
This is one heck of a statement if you ask me, but I didn't want to start another thread. This is a complete procedure to wipe existence of any player from a server. It only needs a CharName, then it goes by itself acquiring all the necessary data. Read the comments when you are lost. Post any errors if you get them.
Code:
USE [PS_GameData]
GO
/****** Object: StoredProcedure [dbo].[ausp_Delete_Player] Script Date: 07/07/2010 00:41:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ausp_Delete_Player]
@CharName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserUID int, @CharID int;
-- temporary table to store basic information about to be deleted characters
DECLARE @del_chars TABLE (UserUID int, UserID varchar(40), CharID int PRIMARY KEY CLUSTERED, CharName varchar(50));
-- get UserUID of the player
SELECT @UserUID=UM.UserUID
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS UM
ON C.UserUID = UM.UserUID
WHERE C.CharName = @CharName AND C.Del=0;
-- no rows returned? player doesn't exist or invalid character name
IF @@ROWCOUNT=0
BEGIN
PRINT 'Character not found.';
return;
END
-- get all characters belonging to player and insert their CharID into temp table
INSERT INTO @del_chars
SELECT UserUID, UserID, CharID, CharName
FROM PS_GameData.dbo.Chars
WHERE UserUID=@UserUID;
BEGIN TRANSACTION;
begin TRY
-- all needed information gathered, it's time to delete
-- let's begin with less important data
DELETE FROM PS_GameData.dbo.CharItems WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.CharQuests WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.CharQuickSlots WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.CharSavePoint WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.CharSkills WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.UserStoredItems WHERE UserUID = @UserUID;
DELETE FROM PS_GameData.dbo.UserMaxGrow WHERE UserUID = @UserUID;
DELETE FROM PS_GameData.dbo.GuildChars WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.FriendChars WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.FriendChars WHERE FriendID IN (SELECT CharID FROM @del_chars);
DELETE PS_GameData.dbo.MarketItems
FROM PS_GameData.dbo.MarketItems AS MID INNER JOIN PS_GameData.dbo.Market AS M
ON M.MarketID = MID.MarketID
WHERE M.CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.Market WHERE CharID IN (SELECT CharID FROM @del_chars);
DELETE FROM PS_GameData.dbo.Chars WHERE UserUID = @UserUID
DELETE FROM PS_UserData.dbo.Users_Detail WHERE UserUID = @UserUID
DELETE FROM PS_UserData.dbo.Users_Master WHERE UserUID = @UserUID
-- save changes to database
COMMIT TRANSACTION;
end TRY
begin CATCH
-- rollback on error
ROLLBACK TRANSACTION;
-- Raise the error with the appropriate message and error severity
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
PRINT @ErrMsg;
PRINT @ErrSeverity;
end CATCH
SET NOCOUNT OFF;
END
SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS OFF;
There's a lot of recurring DELETE and (CharID IN (SELECT CharID FROM @del_chars)) statements. I didn't know how to replace the latter. I hope someone with more knowledge and experience can optimize the procedure.
List players equipped items
I created this procedure to check player's equipment, what lapis is linked and how much it adds to basic status points. I had my doubts about some people 'cheating' their way to become stronger. I think I found myself in error, anyway it is still useful if you want to allow players to use it.
What it does:
- selects all equipped items the player has,
- ignores items in inventory (for now),
- checks what lapis is linked,
- and which stats and by how much it increases,
- sums it up with basic stats and prints everything.
I couldn't come up with any use for this procedure when it turned out to be useless. Then I thought you can actually integrate it with a website and allow players to list their equipment. Might be hard on the DB side though. Still it is worth considering.
Procedure:
Code:
USE [PS_GameData]
GO
/****** Object: StoredProcedure [dbo].[ausp_Check_PlayersEquipment] Script Date: 06/13/2010 12:24:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tomasz Wiącek
-- Create date: <Create Date,,>
-- Description: Lists equipment, lapis linked, current and added status points of a given character.
-- =============================================
CREATE PROCEDURE [dbo].[ausp_Check_PlayersEquipment]
@CharName varchar(50) = '',
@CharID int = 0
AS
BEGIN
SET NOCOUNT ON;
-- variables to store Status Points
-- variables without _p - characters current status points
-- variables with _p - status points added by lapis
DECLARE @Str smallint, @Dex smallint, @Rec smallint, @Int smallint, @Wis smallint, @Luc smallint,
@Str_p smallint, @Dex_p smallint, @Rec_p smallint, @Int_p smallint, @Wis_p smallint, @Luc_p smallint;
-- variables for sp_executesql
DECLARE @stmt nvarchar(1000), @variables nvarchar(500)
-- these variables with hold lapis name and an item it was linked to
DECLARE @ItemName varchar(50),
@GemName1 varchar(50),@GemName2 varchar(50),@GemName3 varchar(50),@GemName4 varchar(50),
@GemName5 varchar(50),@GemName6 varchar(50);
-- variables to hold lapis properties
-- first set is TypeID selected from PS_GameData.dbo.CharItems
-- second set (Const***) holds what lapis adds
DECLARE @Gem1 tinyint,@Gem2 tinyint,@Gem3 tinyint,@Gem4 tinyint,@Gem5 tinyint,@Gem6 tinyint
DECLARE @ConstStr tinyint, @ConstDex tinyint, @ConstRec tinyint, @ConstInt tinyint, @ConstWis tinyint,
@ConstLuc tinyint, @ConstHP tinyint, @ConstMP tinyint, @ConstSP tinyint;
DECLARE @ItemID int
-- let's prepare statements for sp_executesql procedure
SET @stmt = N'
SELECT @GemName=ItemName, @ConstStr=ConstStr, @ConstDex=ConstDex, @ConstRec=ConstRec, @ConstInt=ConstInt,
@ConstWis=ConstWis, @ConstLuc=ConstLuc, @ConstHP=ConstHP, @ConstMP=ConstMP, @ConstSP=ConstSP
FROM PS_GameDefs.dbo.Items WHERE [Type]=30 AND TypeID=@Gem';
SET @variables = N'
@GemName varchar(50) output, @Gem tinyint, @ConstStr tinyint output, @ConstDex tinyint output,
@ConstRec tinyint output, @ConstInt tinyint output, @ConstWis tinyint output, @ConstLuc tinyint output,
@ConstHP tinyint output, @ConstMP tinyint output, @ConstSP tinyint output';
-- and initialize variables
SET @Str_p = 0
SET @Dex_p = 0
SET @Rec_p = 0
SET @Int_p = 0
SET @Wis_p = 0
SET @Luc_p = 0
IF @CharID = 0
SELECT @CharID = CharID FROM PS_GameData.dbo.Chars WHERE CharName=@CharName;
ELSE
SELECT @CharName = CharName FROM PS_GameData.dbo.Chars WHERE CharID=@CharID;
-- let see if the character exists and there are no typing errors
IF @@ROWCOUNT>0
BEGIN
-- setting up a cursor to process characters items (equiped)
DECLARE item_cursor CURSOR FAST_FORWARD FOR
SELECT CI.ItemID, I.ItemName, CI.Gem1, CI.Gem2, CI.Gem3, CI.Gem4, CI.Gem5, CI.Gem6
FROM PS_GameData.dbo.CharItems AS CI INNER JOIN PS_GameDefs.dbo.Items AS I
ON CI.ItemID = I.ItemID
WHERE CI.CharID = @CharID AND CI.Bag=0 --AND Gem1<>0 AND (Gem2<>0 or Gem3<>0 or Gem4<>0 or Gem5<>0 or Gem6<>0);
SELECT ItemID, Gem1, Gem2, Gem3, Gem4, Gem5, Gem6
FROM PS_GameData.dbo.CharItems WHERE CharID=@CharID AND Bag=0
-- initialize cursor and fetch first row
OPEN item_cursor;
FETCH NEXT FROM item_cursor INTO
@ItemID,@ItemName,@Gem1,@Gem2,@Gem3,@Gem4,@Gem5,@Gem6;
-- now let's put it all together and print what items this character has
PRINT '-----------------------------'
PRINT 'Items of: ' + @CharName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
print '----------------------------------------';
print 'Item name: ' + @ItemName;
print '----------------------------------------';
-- if this is an old player not all slots can be taken and some lapis might have been extracted
-- let's make sure we don't process such slots
if @Gem1 <> 0
begin
exec sp_executesql @stmt,@variables,@GemName1 output, @Gem1, @ConstStr output,
@ConstDex output, @ConstRec output, @ConstInt output, @ConstWis output,
@ConstLuc output, @ConstHP output, @ConstMP output, @ConstSP output;
print 'Gem1: ' + @GemName1
if @ConstStr <> 0 SET @Str_p = @Str_p + @ConstStr;
if @ConstDex <> 0 SET @Dex_p = @Dex_p + @ConstDex;
if @ConstRec <> 0 SET @Rec_p = @Rec_p + @ConstRec;
if @ConstInt <> 0 SET @Int_p = @Int_p + @ConstInt;
if @ConstWis <> 0 SET @Wis_p = @Wis_p + @ConstWis;
if @ConstLuc <> 0 SET @Luc_p = @Luc_p + @ConstLuc;
end
if @Gem2 <> 0
begin
exec sp_executesql @stmt,@variables,@GemName2 output,@Gem2, @ConstStr output,
@ConstDex output, @ConstRec output, @ConstInt output, @ConstWis output,
@ConstLuc output, @ConstHP output, @ConstMP output, @ConstSP output;
print 'Gem2: ' + @GemName2
if @ConstStr <> 0 SET @Str_p = @Str_p + @ConstStr;
if @ConstDex <> 0 SET @Dex_p = @Dex_p + @ConstDex;
if @ConstRec <> 0 SET @Rec_p = @Rec_p + @ConstRec;
if @ConstInt <> 0 SET @Int_p = @Int_p + @ConstInt;
if @ConstWis <> 0 SET @Wis_p = @Wis_p + @ConstWis;
if @ConstLuc <> 0 SET @Luc_p = @Luc_p + @ConstLuc;
end
if @Gem3 <> 0
begin
exec sp_executesql @stmt,@variables,@GemName3 output,@Gem3, @ConstStr output,
@ConstDex output, @ConstRec output, @ConstInt output, @ConstWis output,
@ConstLuc output, @ConstHP output, @ConstMP output, @ConstSP output;
print 'Gem3: ' + @GemName3
if @ConstStr <> 0 SET @Str_p = @Str_p + @ConstStr;
if @ConstDex <> 0 SET @Dex_p = @Dex_p + @ConstDex;
if @ConstRec <> 0 SET @Rec_p = @Rec_p + @ConstRec;
if @ConstInt <> 0 SET @Int_p = @Int_p + @ConstInt;
if @ConstWis <> 0 SET @Wis_p = @Wis_p + @ConstWis;
if @ConstLuc <> 0 SET @Luc_p = @Luc_p + @ConstLuc;
end
if @Gem4 <> 0
begin
exec sp_executesql @stmt,@variables,@GemName4 output,@Gem4, @ConstStr output,
@ConstDex output, @ConstRec output, @ConstInt output, @ConstWis output,
@ConstLuc output, @ConstHP output, @ConstMP output, @ConstSP output;
print 'Gem4: '+@GemName4
if @ConstStr <> 0 SET @Str_p = @Str_p + @ConstStr;
if @ConstDex <> 0 SET @Dex_p = @Dex_p + @ConstDex;
if @ConstRec <> 0 SET @Rec_p = @Rec_p + @ConstRec;
if @ConstInt <> 0 SET @Int_p = @Int_p + @ConstInt;
if @ConstWis <> 0 SET @Wis_p = @Wis_p + @ConstWis;
if @ConstLuc <> 0 SET @Luc_p = @Luc_p + @ConstLuc;
end
if @Gem5 <> 0
begin
exec sp_executesql @stmt,@variables,@GemName5 output,@Gem5, @ConstStr output,
@ConstDex output, @ConstRec output, @ConstInt output, @ConstWis output,
@ConstLuc output, @ConstHP output, @ConstMP output, @ConstSP output;
print 'Gem5: '+@GemName5
if @ConstStr <> 0 SET @Str_p = @Str_p + @ConstStr;
if @ConstDex <> 0 SET @Dex_p = @Dex_p + @ConstDex;
if @ConstRec <> 0 SET @Rec_p = @Rec_p + @ConstRec;
if @ConstInt <> 0 SET @Int_p = @Int_p + @ConstInt;
if @ConstWis <> 0 SET @Wis_p = @Wis_p + @ConstWis;
if @ConstLuc <> 0 SET @Luc_p = @Luc_p + @ConstLuc;
end
if @Gem6 <> 0
begin
exec sp_executesql @stmt,@variables,@GemName6 output,@Gem6, @ConstStr output,
@ConstDex output, @ConstRec output, @ConstInt output, @ConstWis output,
@ConstLuc output, @ConstHP output, @ConstMP output, @ConstSP output;
print 'Gem6: ' + @GemName6
if @ConstStr <> 0 SET @Str_p = @Str_p + @ConstStr;
if @ConstDex <> 0 SET @Dex_p = @Dex_p + @ConstDex;
if @ConstRec <> 0 SET @Rec_p = @Rec_p + @ConstRec;
if @ConstInt <> 0 SET @Int_p = @Int_p + @ConstInt;
if @ConstWis <> 0 SET @Wis_p = @Wis_p + @ConstWis;
if @ConstLuc <> 0 SET @Luc_p = @Luc_p + @ConstLuc;
end
-- get new row from the cursor
FETCH NEXT FROM item_cursor INTO
@ItemID,@ItemName,@Gem1,@Gem2,@Gem3,@Gem4,@Gem5,@Gem6
END
-- the cursor is no longer needed, time to close it and free memory
CLOSE item_cursor
DEALLOCATE item_cursor
/* now let's see how many status points the character has */
SELECT @Str = [Str], @Dex = Dex, @Rec = Rec, @Int = [Int], @Wis = Wis, @Luc = Luc
FROM PS_GameData.dbo.Chars WHERE CharName=@CharName
PRINT '----------------------------------------';
PRINT 'Players status points: ';
PRINT 'Str: ' + cast(@Str AS char(3)) + ' (' + cast(@Str_p as char(3)) + ') = ' + cast(@Str + @Str_p as Char(4))
PRINT 'Dex: ' + cast(@Dex AS char(3)) + ' (' + cast(@Dex_p as char(3)) + ') = ' + cast(@Dex + @Dex_p as Char(4))
PRINT 'Rec: ' + cast(@Rec AS char(3)) + ' (' + cast(@Rec_p as char(3)) + ') = ' + cast(@Rec + @Rec_p as Char(4))
PRINT 'Int: ' + cast(@Int AS char(3)) + ' (' + cast(@Int_p as char(3)) + ') = ' + cast(@Int + @Int_p as Char(4))
PRINT 'Wis: ' + cast(@Wis AS char(3)) + ' (' + cast(@Wis_p as char(3)) + ') = ' + cast(@Wis + @Wis_p as Char(4))
PRINT 'Luc: ' + cast(@Luc AS char(3)) + ' (' + cast(@Luc_p as char(3)) + ') = ' + cast(@Luc + @Luc_p as Char(4))
PRINT '----------------------------------------';
END
ELSE
BEGIN
PRINT 'Character ' + @CharName + ' does not exists.'
END
END
New Version
Version II of the above procedure. It does the same thing but is entirely different. You can integrate it with your website as the results are sent to PHP or whatever you are using. I am planing to write a PHP website which will sort all the information.
To remind beginners. It is up to you to implement it. I will not provide any assistance in doing so.
I tested the procedure and it works. It works with PHP as well. I was able to receive, switch between results and print the data. I haven't noticed any errors, but if you get one post it here. Any optimizations are welcome.
Procedure Ver.2
Code:
USE [PS_GameData]
GO
/****** Object: StoredProcedure [dbo].[ausp_Check_PlayersEquipment] Script Date: 06/13/2010 12:24:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tomasz Wiącek
-- Create date: 12.I.2011
-- Description: Lists equipment, lapis linked, current and added status points of a given character.
-- =============================================
CREATE PROCEDURE [dbo].[ausp_Read_PlayersEquipment_V2]
@CharID int
AS
BEGIN
SET NOCOUNT ON;
-- first get player UserUID
DECLARE @UserUID int;
SELECT @UserUID = UserUID FROM PS_GameData.dbo.Chars WHERE CharID = @CharID;
-- temp tables to store player items - equipped, in backpack, in warehouse
DECLARE @items_equipped TABLE (ItemID int, ItemName varchar(30), Gem1 varchar(30), Gem2 varchar(30),
Gem3 varchar(30), Gem4 varchar(30), Gem5 varchar(30), Gem6 varchar(30));
DECLARE @items_backpack TABLE (ItemID int, ItemName varchar(30));
DECLARE @items_warehouse TABLE (ItemID int, ItemName varchar(30));
-- temp table to store lapis properties
DECLARE @lapis_props TABLE (TypeID smallint, ItemName varchar(30), ConstStr tinyint, ConstDex tinyint,
ConstRec tinyint, ConstInt tinyint, ConstWis tinyint, ConstLuc tinyint, ConstHP tinyint,
ConstMP tinyint, ConstSP tinyint);
/*
Now get all needed information about player's items and lapis linked in equipped gear.
- get player's equiped items
*/
INSERT @items_equipped
SELECT CI.ItemID, I.ItemName, CI.Gem1, CI.Gem2, CI.Gem3, CI.Gem4, CI.Gem5, CI.Gem6
FROM PS_GameData.dbo.CharItems AS CI INNER JOIN PS_gamedefs.dbo.Items AS I
ON CI.ItemID = I.ItemID AND CI.Bag=0
WHERE CI.CharID = @CharID
ORDER BY CI.Bag, CI.Slot;
-- get player's items from backpack
INSERT INTO @items_backpack
SELECT CI.ItemID, I.ItemName
FROM PS_GameData.dbo.CharItems AS CI INNER JOIN PS_gamedefs.dbo.Items AS I
ON CI.ItemID = I.ItemID
WHERE CI.CharID = @CharID AND CI.Bag IN (1,2,3,4,5)
ORDER BY CI.Bag, I.Slot;
-- get player's items from warehouse
INSERT INTO @items_warehouse
SELECT USI.ItemID, I.ItemName
FROM PS_GameData.dbo.UserStoredItems AS USI INNER JOIN PS_gamedefs.dbo.Items AS I
ON USI.ItemID = I.ItemID
WHERE USI.UserUID = @UserUID
ORDER BY USI.Slot;
/*
Get lapis properties for each lapis linked to equipped gear.
Info is stored by TypeID of lapis.
*/
INSERT INTO @lapis_props
SELECT TypeID, ItemName, ConstStr, ConstDex, ConstRec, ConstWis, ConstInt, ConstLuc, ConstHP, ConstMP, ConstSP
FROM PS_gamedefs.dbo.Items
WHERE [Type]=30 AND TypeID IN ( SELECT DISTINCT Gem.Gem1 FROM
(SELECT DISTINCT Gem1 FROM @items_equipped UNION ALL Select DISTINCT Gem2 FROM @items_equipped UNION ALL
SELECT DISTINCT Gem3 FROM @items_equipped UNION ALL SELECT DISTINCT Gem4 FROM @items_equipped UNION ALL
SELECT DISTINCT Gem5 FROM @items_equipped UNION ALL SELECT DISTINCT Gem6 FROM @items_equipped) AS Gem)
/*
Create table to store player's status information.
- without _p - characters current status points
- with _p - status points added by lapis (summed)
*/
DECLARE @player_stats TABLE (
CharName varchar(60),
[Str] smallint, Dex smallint, Rec smallint, [Int] smallint, Wis smallint, Luc smallint,
Str_p smallint DEFAULT(0), Dex_p smallint DEFAULT(0), Rec_p smallint DEFAULT(0),
Int_p smallint DEFAULT(0), Wis_p smallint DEFAULT(0), Luc_p smallint DEFAULT(0));
-- fill above table with player's status information
INSERT INTO @player_stats (CharName, [Str], Dex, Rec, [Int], Wis, Luc)
SELECT CharName, [Str], Dex, Rec, [Int], Wis, Luc
FROM PS_GameData.dbo.Chars
WHERE CharID=@CharID;
/*
Add points added by lapis and store the result in _p fields in @player_stats table.
Variables found below are used to collect data about the amount of status points added
by lapis. It is used then to update @player_stats table _p fields.
*/
DECLARE @Str smallint, @Dex smallint, @Rec smallint, @Int smallint, @Wis smallint, @Luc smallint,
@Str_p smallint, @Dex_p smallint, @Rec_p smallint, @Int_p smallint, @Wis_p smallint, @Luc_p smallint;
SELECT @Str = SUM(LP.ConstStr), @Dex = SUM(LP.ConstDex), @Rec = SUM(LP.ConstRec),
@Int = SUM(LP.ConstInt), @Wis = SUM(LP.ConstWis), @Luc = SUM(LP.ConstLuc)
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem1 = LP.TypeID
SET @Str_p=@Str;
SET @Dex_p=@Dex;
SET @Rec_p=@Rec;
SET @Int_p=@Int;
SET @Wis_p=@Wis;
SET @Luc_p=@Luc;
SELECT @Str = SUM(LP.ConstStr), @Dex = SUM(LP.ConstDex), @Rec = SUM(LP.ConstRec),
@Int = SUM(LP.ConstInt), @Wis = SUM(LP.ConstWis), @Luc = SUM(LP.ConstLuc)
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem2 = LP.TypeID
SET @Str_p = @Str_p + @Str;
SET @Dex_p = @Dex_p + @Dex;
SET @Rec_p = @Rec_p + @Rec;
SET @Int_p = @Int_p + @Int;
SET @Wis_p = @Wis_p + @Wis;
SET @Luc_p = @Luc_p + @Luc;
SELECT @Str = SUM(LP.ConstStr), @Dex = SUM(LP.ConstDex), @Rec = SUM(LP.ConstRec),
@Int = SUM(LP.ConstInt), @Wis = SUM(LP.ConstWis), @Luc = SUM(LP.ConstLuc)
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem3 = LP.TypeID
SET @Str_p = @Str_p + @Str;
SET @Dex_p = @Dex_p + @Dex;
SET @Rec_p = @Rec_p + @Rec;
SET @Int_p = @Int_p + @Int;
SET @Wis_p = @Wis_p + @Wis;
SET @Luc_p = @Luc_p + @Luc;
SELECT @Str = SUM(LP.ConstStr), @Dex = SUM(LP.ConstDex), @Rec = SUM(LP.ConstRec),
@Int = SUM(LP.ConstInt), @Wis = SUM(LP.ConstWis), @Luc = SUM(LP.ConstLuc)
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem4 = LP.TypeID
SET @Str_p = @Str_p + @Str;
SET @Dex_p = @Dex_p + @Dex;
SET @Rec_p = @Rec_p + @Rec;
SET @Int_p = @Int_p + @Int;
SET @Wis_p = @Wis_p + @Wis;
SET @Luc_p = @Luc_p + @Luc;
SELECT @Str = SUM(LP.ConstStr), @Dex = SUM(LP.ConstDex), @Rec = SUM(LP.ConstRec),
@Int = SUM(LP.ConstInt), @Wis = SUM(LP.ConstWis), @Luc = SUM(LP.ConstLuc)
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem5 = LP.TypeID
SET @Str_p = @Str_p + @Str;
SET @Dex_p = @Dex_p + @Dex;
SET @Rec_p = @Rec_p + @Rec;
SET @Int_p = @Int_p + @Int;
SET @Wis_p = @Wis_p + @Wis;
SET @Luc_p = @Luc_p + @Luc;
SELECT @Str = SUM(LP.ConstStr), @Dex = SUM(LP.ConstDex), @Rec = SUM(LP.ConstRec),
@Int = SUM(LP.ConstInt), @Wis = SUM(LP.ConstWis), @Luc = SUM(LP.ConstLuc)
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem6 = LP.TypeID
SET @Str_p = @Str_p + @Str;
SET @Dex_p = @Dex_p + @Dex;
SET @Rec_p = @Rec_p + @Rec;
SET @Int_p = @Int_p + @Int;
SET @Wis_p = @Wis_p + @Wis;
SET @Luc_p = @Luc_p + @Luc;
UPDATE @player_stats SET Str_p=@Str_p, Dex_p=@Dex_p, Rec_p=@Rec_p, Int_p=@Int_p, Wis_p=@Wis_p, Luc_p=@Luc_p;
/*
Because of small error when declaring @items_equipped table (Gem1-6 is varchar instead of tinyint),
all TypeID's of lapis will be updated to LapisName.
*/
UPDATE @items_equipped SET Gem1 = LP.ItemName
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem1=LP.TypeID
UPDATE @items_equipped SET Gem2 = LP.ItemName
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem2=LP.TypeID
UPDATE @items_equipped SET Gem3 = LP.ItemName
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem3=LP.TypeID
UPDATE @items_equipped SET Gem4 = LP.ItemName
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem4=LP.TypeID
UPDATE @items_equipped SET Gem5 = LP.ItemName
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem5=LP.TypeID
UPDATE @items_equipped SET Gem6 = LP.ItemName
FROM @items_equipped AS IE INNER JOIN @lapis_props AS LP
ON IE.Gem6=LP.TypeID
/*
Select all the data for the PHP script to process.
*/
SELECT CharName, [Str], Dex, Rec, [Int], Wis, Luc, Str_p, Dex_p, Rec_p, Int_p, Wis_p, Luc_p FROM @player_stats;
SELECT ItemName, Gem1, Gem2, Gem3, Gem4, Gem5, Gem6 FROM @items_equipped;
SELECT ItemName FROM @items_backpack;
SELECT ItemName FROM @items_warehouse;
SET NOCOUNT OFF;
END
SET ANSI_NULLS OFF;
SET QUOTED_IDENTIFIER OFF;
![[GM]Father's Avatar](https://www.elitepvpers.com/forum/customavatars/avatar2710919_1.gif){kind=avatar}
