gth:databasequeries

Database Queries

Various database queries that you will want to learn or bookmark. Run these in Server Management Studio.

Create Account

-- Declare variables for username and password
DECLARE @username VARCHAR(20) = 'username';
DECLARE @password VARCHAR(50) = 'password';

-- Calculate the new account_code
DECLARE @account_code INT;
SET @account_code = (SELECT COUNT(*) FROM gth_account.dbo.tbl_account) + 1;

-- Check if the username already exists
IF NOT EXISTS (SELECT 1 FROM gth_account.dbo.tbl_account WHERE username = @username)
BEGIN
    -- Insert into the table using the calculated account_code and declared variables
    INSERT INTO gth_account.dbo.tbl_account 
    (account_code, username, gth_id, gth_pw, create_date, last_login, IcNo, account_level, access, login_count) 
    VALUES 
    (@account_code, @username, @username, @password, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 12345, 0, 0, 0);
END
ELSE
BEGIN
    PRINT 'Username already exists. Please choose a different username.';
END

Player Position

-- Declare variables for the new coordinates, world index, and character name
DECLARE @newPosX REAL = 8582; -- Replace with the desired X coordinate
DECLARE @newPosY REAL = 13355; -- Replace with the desired Y coordinate
DECLARE @newPosZ REAL = 3072; -- Replace with the desired Z coordinate
DECLARE @newWorldIdx TINYINT = 0; -- Replace with the desired world index
DECLARE @characterName VARCHAR(20) = 'RaGEZONE'; -- Replace with the character's name

-- Check if the character name exists
IF EXISTS (SELECT 1 FROM [dbo].[tbl_game] WHERE [name] = @characterName)
BEGIN
    -- Update the player's coordinates and world index
    UPDATE [dbo].[tbl_game]
    SET 
        [posX] = @newPosX,
        [posY] = @newPosY,
        [posZ] = @newPosZ,
        [worldIdx] = @newWorldIdx
    WHERE 
        [name] = @characterName;

    PRINT 'Player coordinates and world index updated successfully.';
END
ELSE
BEGIN
    -- Print error message if character name does not exist
    PRINT 'Error: Character name does not exist.';
END

Player Name

-- Declare variables for the old and new names
DECLARE @oldName VARCHAR(20) = 'old_character_name'; -- Replace with the current character's name
DECLARE @newName VARCHAR(20) = 'new_character_name'; -- Replace with the desired new character's name

-- Check if the old character name exists
IF EXISTS (SELECT 1 FROM [dbo].[tbl_game] WHERE [name] = @oldName)
BEGIN
    -- Check if the new character name already exists
    IF EXISTS (SELECT 1 FROM [dbo].[tbl_game] WHERE [name] = @newName)
    BEGIN
        -- Print error message if the new character name already exists
        PRINT 'Error: The new character name already exists. Please choose a different name.';
    END
    ELSE
    BEGIN
        -- Update the character's name
        UPDATE [dbo].[tbl_game]
        SET [name] = @newName
        WHERE [name] = @oldName;

        -- Print success message
        PRINT 'Success: The character name has been changed successfully.';
    END
END
ELSE
BEGIN
    -- Print error message if the old character name does not exist
    PRINT 'Error: The old character name does not exist.';
END

  • gth/databasequeries.txt
  • Last modified: 2024/07/06 04:22
  • by ron