GM/unGM stored procedure

[MashiroShiina]

This is my first release and I hope you guys like it.

First of all, for all of you having problems with gm's ip check.

USE [Account]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cabal_sp_gm_ip_check](@UserIP CHAR(16), @Registered BIT OUTPUT)
AS
BEGIN
SET @Registered = 1;
END;

Credits to Alphakilo23.

This is the procedure I made for creating a gm account:

USE [Server01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[gm_account_by_mashiroshiina]
( 
    @charname varchar(50),
    @tag varchar(20)
    
)
AS
BEGIN
BEGIN TRAN


begin


declare @usernum int


--getting usernum
select @usernum = UserNum
from Account.dbo.cabal_auth_table,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);


--transmuter
exec CabalCash.dbo.up_AddMyCashItemByItem @usernum,'0','1','644','0','31';
--combo skill
exec CabalCash.dbo.up_AddMyCashItemByItem @usernum,'0','1','2105','0','31';
--Bike skill
exec CabalCash.dbo.up_AddMyCashItemByItem @usernum,'0','1','2106','0','31';
--GM stick
exec CabalCash.dbo.up_AddMyCashItemByItem @usernum,'0','1','4657','0','31';


--GM buff
update Server01.dbo.cabal_skilllist_table
set Data=Data+0xCE01012500
from Server01.dbo.cabal_skilllist_table a, Server01.dbo.cabal_character_table b, Account.dbo.cabal_auth_table
where Usernum=floor(a.CharacterIdx/8) and a.CharacterIdx=b.CharacterIdx and Name=@charname


--updating char fields
update  Server01.dbo.cabal_character_table
set Nation =3,
MapsBField =4095,
WarpBField =4095,
Style =(style+152),
Lev =190,
Reputation =2000000000,
Alz =10000000,
Str =9999,
Dex =9999,
Int =9999,
SwdPNT =99999999,
MagPNT =99999999 ,
Rank=2570
from Account.dbo.cabal_auth_table,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);


--update ecoin
update CabalCash.dbo.CashAccount
set Cash=1000
from CabalCash.dbo.CashAccount,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);


--change name/add tag
update  Server01.dbo.cabal_character_table
set Name = (@tag+Name)
from Account.dbo.cabal_auth_table,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);




end
COMMIT TRAN    
END

This is the one for removing gm permissions.

USE [Server01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ungm_account_by_mashiroshiina]
( 
    @charname varchar(50),
    @tag varchar(20)
        
)
AS
BEGIN
BEGIN TRAN


begin


--removing char fields
update  Server01.dbo.cabal_character_table
set Nation =1,
Lev =1
from Account.dbo.cabal_auth_table,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);


--removing account web access
update Account.dbo.cabal_auth_table
set Password=pwdencrypt('exgmpassword')
from Account.dbo.cabal_auth_table,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);


--remove ecoin
update CabalCash.dbo.CashAccount
set Cash=0
from CabalCash.dbo.CashAccount,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);


update  Server01.dbo.cabal_character_table
set Name = REPLACE(Name,@tag,'')
from Account.dbo.cabal_auth_table,Server01.dbo.cabal_character_table
where Name=@charname and UserNum=floor(CharacterIdx/8);




end
COMMIT TRAN    
END

In order to run it, all you need to execute is:

exec Server01.dbo.gm_account_by_mashiroshiina 'CHARNAME','TAG'

exec Server01.dbo.ungm_account_by_mashiroshiina 'CHARNAME','TAG'

As you may noticed, I'm giving everything a GM needs to do their job. If I'm missing something, just append it ;)

[MentaL]

[devit65]

CRATE PROCEDURE [dbo].[gm_account_by_mashiroshiina]


succes work

[MashiroShiina]

You're right, forgot to modify that part. Thank you :)
Updated, now it should work fine C:

[Kome Keto]

Very good, this is not my task on the server but is good to have :D...
To fit our need i'm only going to replace the tags from names with titles, this is how we are doing now :D, i dont know if you know but [] are reserved characters on MSSQL, and can be a problem when using searching with like: SELECT * FROM ABC.cabal_character_table WHERE name LIKE '[GM]%'.

Keep good work, ty :D

[MashiroShiina]

You're right about the problem on like '[GM]%'
But it works perfectly with this query:

select * from cabal_character_table
where name like '%GM]%'

Using acc roles is also good for finding staff members :)

Btw, I'm also using titles but I removed it from the sp to avoid issues since we have it customized.

Thank you for the tip :)

[asgat]

how to use this sir? is it just write in mssql? or?

- - - Updated - - -

how to use this sir? is it just write in mssql? or?

[xDarkMoon]

how to use this sir? is it just write in mssql? or?

- - - Updated - - -

how to use this sir? is it just write in mssql? or?

Read Mashiro's post?

[sasuke]

and good works but no longer work skills