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