ALTER PROCEDURE [dbo].[CREATE_NEW_CHAR]

@nRet       SMALLINT OUTPUT,
@AccountID  CHAR(21),
@INDEX      tinyint,
@CharID CHAR(21),
@Race       tinyint,
@Class  SMALLINT,
@Hair       tinyint,
@Face       tinyint,
@Str        tinyint,
@Sta        tinyint,
@Dex        tinyint,
@Intel      tinyint,
@Cha        tinyint

AS

DECLARE @ROW tinyint, @Nation tinyint, @Zone tinyint, @PosX INT, @PosZ INT, @x INT, @c INT, @bValid tinyint
    SET @ROW = 0    SET @Nation = 0  SET @Zone = 0  SET @PosX = 0 SET @PosZ = 0
    SET @x = 1
    SET @bValid = 1

        IF LEN(LTRIM(@CharID)) < 3  -- LEN() does an RTRIM()
        BEGIN
          SET @nRet = 7
          RETURN
        END

    WHILE (@x <= LEN(@CharID))
    BEGIN
        SET @c = ASCII(SUBSTRING(@CharID, @x, 1))
        IF ((@c NOT BETWEEN ASCII('a') AND ASCII('z'))
            AND (@c NOT BETWEEN ASCII('A') AND ASCII('Z'))
            AND (@c NOT BETWEEN ASCII('0') AND ASCII('9'))
            AND (@c NOT IN (ASCII('_'), ASCII('-'), ASCII(':'))))
        BEGIN
            SET @bValid = 0
            BREAK
        END
        SET @x = @x + 1
    END

    IF (@bValid = 0)
    BEGIN
        SET @nRet = 5
        RETURN
    END

    SELECT @Nation = bNation, @ROW = bCharNum FROM ACCOUNT_CHAR WHERE strAccountID = @AccountID

    IF @ROW >= 5    SET @nRet =  1

    IF @Nation = 1 AND @Race > 10   SET @nRet = 2
    ELSE IF @Nation = 2 AND @Race < 10  SET @nRet = 2
    ELSE IF @Nation <>1 AND @Nation <> 2    SET @nRet = 2

    IF @nRet > 0
        RETURN

    SELECT @ROW = COUNT(*) FROM USERDATA WHERE strUserId = @CharID
    IF @ROW > 0
    BEGIN
        SET @nRet =  3
        RETURN
    END

    --SET @Zone = @Nation
    SET @Zone=21
    SELECT @PosX = InitX, @PosZ = InitZ  FROM ZONE_INFO WHERE ZoneNo = @Zone


BEGIN TRAN
    IF @INDEX = 0
        UPDATE ACCOUNT_CHAR SET strCharID1 = @CharID, bCharNum = bCharNum + 1 WHERE strAccountID = @AccountID
    ELSE IF @INDEX = 1
        UPDATE ACCOUNT_CHAR SET strCharID2 = @CharID, bCharNum = bCharNum + 1 WHERE strAccountID = @AccountID
    ELSE IF @INDEX = 2
        UPDATE ACCOUNT_CHAR SET strCharID3 = @CharID, bCharNum = bCharNum + 1 WHERE strAccountID = @AccountID
    ELSE IF @INDEX = 3
        UPDATE ACCOUNT_CHAR SET strCharID4 = @CharID, bCharNum = bCharNum + 1 WHERE strAccountID = @AccountID
    ELSE IF @INDEX = 4
        UPDATE ACCOUNT_CHAR SET strCharID5 = @CharID, bCharNum = bCharNum + 1 WHERE strAccountID = @AccountID


    INSERT INTO USERDATA (strUserId, Nation, Race, Class, HairColor, Face, Strong, Sta, Dex, Intel, Cha, Zone, PX, PZ )
    VALUES   (@CharID, @Nation, @Race, @Class, @Hair, @Face, @Str, @Sta, @Dex, @Intel, @Cha, @Zone, @PosX, @PosZ )


    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRAN
        SET @nRet =  4
        RETURN
    END

COMMIT TRAN
SET @nRet =  0