[RELEASE] Guild Leader change - SQL version

Quote:

Originally Posted by ProfNerwosol

Here's a Guild Leader change script wrote in SQL. It is a stored procedure so you can call it from php or whatever you are using.

There are many comments so you shouldn't be lost when reading through it. I haven't tested it. What I did was rewrite old procedure I had lying around. If you get any errors, post them here.

Code:

USE PS_GAMEDATA
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tomasz Wiacek
-- Create date: 10.IV.2010
-- Description:	Change guild leader
/* 
Provide the procedure with character name of new
and old guild leader - @OldGuildLeader is old GL
and @NewGuildLeader is new GL respectively.
The rest will be done by the procedure.
*/
-- =============================================
CREATE PROCEDURE dbo.ausp_ChangeGuildLeader 
	
	@OldGuildLeader varchar(30),
	@NewGuildLeader varchar(30)

AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @GuildID int				-- GuildID where change of leadership will take place	
	DECLARE @MasterCharID int			-- stores new GL CharID
	DECLARE @MasterUserID varchar(30)	-- stores new GL UserID
	DECLARE @OldGLCharID int			-- used to store CharID of current GL
	DECLARE @MasterName varchar(40)		-- stores new GL CharName

	-- select GuildID and old GuildLeader CharID
	SELECT @GuildID = GuildID, @OldGLCharID = MasterCharID
	FROM PS_GameData.dbo.Guilds
	WHERE MasterName = @OldGuildLeader;
	
	-- check if any results were returned
	IF @@ROWCOUNT = 0
	begin
		PRINT 'Guild leader - ' + @OldGuildLeader + ' does not exist.';
	end
	ELSE
	begin
		-- get CharID, UserID and CharName of new GuildLeader
		SELECT @MasterCharID = CharID, @MasterUserID = UserID, @MasterName = CharName
		FROM PS_GameData.dbo.Chars
		WHERE CharName = @NewGuildLeader;
		
		-- if new GuildLeader CharName is correct and it exists (result was returned by above statement)
		-- and new GuildLeader is part of the guild execute leadership change
		IF @@ROWCOUNT <> 0 AND @MasterCharID IN (SELECT CharID FROM PS_GameData.dbo.GuildChars WHERE GuildID=@GuildID AND Del=0 AND (LeaveDate IS NULL OR LeaveDate=0))
		begin
			-- update information about who's the current GuildLeader
			UPDATE PS_GameData.dbo.Guilds 
			SET MasterCharID = @MasterCharID, MasterUserID = @MasterUserID, MasterName = @MasterName
			WHERE GuildID = @GuildID;
			
			-- make new GL leader of the guild
			UPDATE PS_GameData.dbo.GuildChars
			SET GuildLevel = 1 WHERE CharID = @MasterCharID AND GuildID = @GuildID;
			
			-- give old GL GuildLevel=3 to be on the safe side if there are too many Lv2's in guild
			UPDATE PS_GameData.dbo.GuildChars
			SET GuildLevel = 3 WHERE CharID = @OldGLCharID AND GuildID = @GuildID;
			
			PRINT 'Guild leadership between - ' + @OldGuildLeader + ' - and - ' + @NewGuildLeader + ' was changed.'
			
		end
		ELSE
		begin
			PRINT 'No such character: ' + @NewGuildLeader + ' or character is not part of the guild.';
		end
	end



END

To those new to SQL, paste the above code as new query in Server Management Studio and execute it. To run it do this:

Code:

EXEC PS_GameData..ausp_ChangeGuildLeader 'OldGLName','NewGLName'

replacing the words in-between quotes with real character names.

NOTE:
The changes won't take effect until server restart.

heya was trying this insted of doing it manualy. but when i did this. i got:

Msg 8144, Level 16, State 2, Procedure ausp_ChangeGuildLeader, Line 0
Procedure or function ausp_ChangeGuildLeader has too many arguments specified.
and yes i used the new better sql query but the old

Code:

EXEC PS_GameData..ausp_ChangeGuildLeader 'OldGLName','NewGLName'