cs_bannedaddress_createupdatedelete.prc

来自「community server 源码」· PRC 代码 · 共 97 行

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_BannedAddress_CreateUpdateDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_BannedAddress_CreateUpdateDelete]
GO

CREATE Procedure [dbo].[cs_BannedAddress_CreateUpdateDelete]
	  @SettingsID		INT
	, @NetworkAddress	NVARCHAR(50) 
	, @BannedDate		DATETIME
	, @ActionType		INT
AS
	DECLARE @ID INT
	DECLARE @ERROR INT
	DECLARE @ROWCOUNT INT
	
	-- CREATE
	IF @ActionType = 0 
	BEGIN
		IF EXISTS( SELECT * FROM cs_BannedAddresses where SettingsID = @SettingsID and NetworkAddress = @NetworkAddress )
			exec cs_BannedAddress_CreateUpdateDelete @SettingsID, @NetworkAddress, @BannedDate, 1 
		else
		BEGIN
		
			INSERT INTO cs_BannedAddresses VALUES( @SettingsID, @NetworkAddress, @BannedDate )

			SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
			IF( @ERROR <> 0 )
			BEGIN
				RAISERROR( 'An error occurred while trying to create a new cs_BannedAddress record.', 16, 1 )
				RETURN @ERROR
			END
			ELSE
			BEGIN
				RETURN 0
			END
		END
	END
	ELSE IF( @ActionType = 1 )
	BEGIN
		-- UPDATE
		IF NOT EXISTS( SELECT * FROM cs_BannedAddresses WHERE SettingsID = @SettingsID and NetworkAddress = @NetworkAddress )
			exec cs_BannedAddress_CreateUpdateDelete @SettingsID, @NetworkAddress, @BannedDate, 0 
		ELSE
		BEGIN
			UPDATE cs_BannedAddresses SET
				SettingsID = @SettingsID,
				NetworkAddress = @NetworkAddress,
				BannedDate = @BannedDate
			WHERE
					SettingsID = @SettingsID 
				AND NetworkAddress = @NetworkAddress
				
			SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
			IF( @ERROR <> 0 )
			BEGIN
				RAISERROR('Could not update the cs_BannedAddress record for SettingsID %d and NetworkAddress of %d', 16, 1, @SettingsID, @NetworkAddress )
				RETURN @ERROR
			END
			ELSE
			BEGIN
				RETURN 0				
			END
		END
	END
	ELSE IF( @ActionType = 2 )
	BEGIN
		-- DELETE
		DELETE cs_BannedAddresses
		WHERE SettingsID = @SettingsID
			AND NetworkAddress = @NetworkAddress
			
		SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
		IF( @ROWCOUNT = 1 )
			RETURN 0
		ELSE
			RETURN 1
		
	END
	ELSE
	BEGIN
		RAISERROR( 'The ActionType value should be between 0 and 2. The value of %d was not within this range', 16, 1, @ActionType )
		RETURN 1
	END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


GRANT  EXECUTE  ON [dbo].[cs_BannedAddress_CreateUpdateDelete]  TO [public]
GO