[Release]Character name changer (PHP)

SET ANSI_NULLS ON;


GO
SET QUOTED_IDENTIFIER ON;


GO
--╔═══╗	╔═══╗	╔═══╗	╔═══╗	╔═══╗	
--║╔═╗║	║╔═╗║	╚╗╔╗║	║╔══╝	╚╗╔╗║	
--║║ ╚╝	║║ ║║	 ║║║║	║╚══╗	 ║║║║	
--║║ ╔╗	║║ ║║	 ║║║║	║╔══╝	 ║║║║	
--║╚═╝║	║╚═╝║	╔╝╚╝║	║╚══╗	╔╝╚╝║	
--╚═══╝	╚═══╝	╚═══╝	╚═══╝	╚═══╝	
--╔══╗	╔╗  ╔╗
--║╔╗║	║╚╗╔╝║
--║╚╝╚╗	╚╗╚╝╔╝           
--║╔═╗║	 ╚╗╔╝
--║╚═╝║	  ║║
--╚═══╝	  ╚╝
--╔╗		╔═══╗		╔═══╗		╔════╗		╔════╗		╔╗ ╔╗	╔══╗	╔═══╗		╔═══╗
--║║		║╔═╗║		║╔═╗║		║╔╗╔╗║		║╔╗╔╗║		║║ ║║	╚╣╠╝	║╔══╝		║╔══╝
--║║		║║─║║		║╚══╗		╚╝║║╚╝		╚╝║║╚╝		║╚═╝║	 ║║		║╚══╗		║╚══╗
--║║ ╔╗		║╚═╝║		╚══╗║		  ║║	  	  ║║		║╔═╗║	 ║║		║╔══╝		║╔══╝
--║╚═╝║		║╔═╗║		║╚═╝║		  ║║	  	  ║║		║║ ║║	╔╣╠╗	║╚══╗		║║
--╚═══╝		╚╝ ╚╝		╚═══╝		  ╚╝    	  ╚╝		╚╝ ╚╝	╚══╝	╚═══╝		╚╝
ALTER PROCEDURE [dbo].[_RenameCharName]
@OldName VARCHAR (MAX), @NewName VARCHAR (MAX)
AS
DECLARE @NameLength AS INT;
SET @NameLength = len(@NewName);
IF (@NameLength < 2)
    BEGIN
        SELECT 'Character name should be more than 2 characters' AS 'ERROR';
        RETURN;
    END
DECLARE @CharID AS INT;
SET @CharID = 0;
SELECT @CharID = CharID
FROM   _Char
WHERE  CharName16 = @OldName;
IF (@@rowcount = 0
    OR @CharID IS NULL
    OR @CharID = 0)
    BEGIN
        SELECT 'Such character does not exist' AS 'ERROR';
        RETURN;
    END
SET XACT_ABORT ON;
BEGIN TRANSACTION;
IF (EXISTS (SELECT CharID
            FROM   _Char WITH (UPDLOCK)
            WHERE  CharName16 = @NewName))
    BEGIN
        ROLLBACK;
        SELECT 'Character name already exists' AS 'ERROR';
        RETURN;
    END
DECLARE @Old_Name AS VARCHAR (64);
SELECT @Old_Name = CharName16
FROM   _Char
WHERE  CharID = @CharID;
UPDATE  _Char
    SET CharName16 = @NewName
WHERE   CharID = @CharID;
IF (@@error <> 0
    OR @@rowcount = 0)
    BEGIN
        ROLLBACK;
        SELECT 'Renaming failed contact admin';
        RETURN;
    END
UPDATE  _CharNameList
    SET CharID = 0
WHERE   CharID = @CharID;
INSERT  INTO _CharNameList
VALUES (@NewName, @CharID);
UPDATE  _Friend
    SET friendcharname = @NewName
WHERE   friendcharid = @charid;
UPDATE  _GuildMember
    SET charname = @NewName
WHERE   charid = @charid;
UPDATE  _Memo
    SET fromcharname = @NewName
WHERE   fromcharname = @old_name;
UPDATE  _TrainingCampMember
    SET charname = @NewName
WHERE   charid = @charid;
DECLARE @cos_id AS INT;
DECLARE cos_cursor CURSOR FAST_FORWARD
    FOR SELECT id
        FROM   _charcos
        WHERE  ownercharid = @CharID;
OPEN cos_cursor;
FETCH NEXT FROM cos_cursor INTO @cos_id;
WHILE (@@fetch_status = 0)
    BEGIN
        UPDATE  _Items
            SET CreaterName = @NewName
        WHERE   ID64 IN (SELECT ItemID
                         FROM   _InvCOS
                         WHERE  COSID = @cos_id
                                AND ItemID > 0)
                AND CreaterName = @old_Name;
        IF (@@error <> 0)
            BEGIN
                CLOSE cos_cursor;
                DEALLOCATE cos_cursor;
                ROLLBACK;
                RETURN -5;
            END
        FETCH NEXT FROM cos_cursor INTO @cos_id;
    END
CLOSE cos_cursor;
DEALLOCATE cos_cursor;
DECLARE pc_inv_cursor CURSOR FAST_FORWARD
    FOR SELECT it.Data
        FROM   _Inventory AS inv
               INNER JOIN
               _Items AS it
               ON inv.ItemID = it.ID64
        WHERE  (inv.CharID = @CharID
                AND inv.Slot >= 13
                AND inv.ItemID > 0)
               AND (it.Data <> 0)
               AND (EXISTS (SELECT TOP 1 ID
                            FROM   _RefObjCommon
                            WHERE  ID = it.RefItemID
                                   AND TypeID1 = 3
                                   AND TypeID2 = 2));
OPEN pc_inv_cursor;
FETCH NEXT FROM pc_inv_cursor INTO @cos_id;
WHILE (@@fetch_status = 0)
    BEGIN
        UPDATE  _Items
            SET CreaterName = @NewName
        WHERE   ID64 IN (SELECT ItemID
                         FROM   _InvCOS
                         WHERE  COSID = @cos_id
                                AND ItemID > 0)
                AND CreaterName = @old_Name;
        IF (@@error <> 0)
            BEGIN
                CLOSE pc_inv_cursor;
                DEALLOCATE pc_inv_cursor;
                ROLLBACK;
                RETURN -6;
            END
        FETCH NEXT FROM pc_inv_cursor INTO @cos_id;
    END
CLOSE pc_inv_cursor;
DEALLOCATE pc_inv_cursor;


COMMIT TRANSACTION;
SELECT 'Character name changed from ' + @OldName + ' to ' + @NewName;
 INSERT  _CharacterRenaming_Log
        VALUES (@old_name, @NewName, getdate());