cs_bannednetwork_createupdatedelete.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE Procedure [dbo].[cs_BannedNetwork_CreateUpdateDelete]
	@BannedNetworkID	INT = 0 OUTPUT,
	@SettingsID			INT,
	@StartingAddress	NVARCHAR(50)=  null,
	@EndingAddress		NVARCHAR(50) = null,
	@BannedDate			DATETIME,
	@ActionType			INT
AS
	DECLARE @ID INT
	DECLARE @ERROR INT
	DECLARE @ROWCOUNT INT
	
	-- CREATE
	IF @ActionType = 0 
	BEGIN
		IF EXISTS( SELECT * FROM cs_BannedNetworks where SettingsID = @SettingsID and BannedNetworkID = @BannedNetworkID )
			exec cs_BannedNetwork_CreateUpdateDelete @BannedNetworkID OUTPUT, @SettingsID, @StartingAddress, @EndingAddress, @BannedDate, 1 
		ELSE
		BEGIN
		
			INSERT INTO cs_BannedNetworks (
				SettingsID,
				StartingAddress,
				EndingAddress,
				BannedDate
			) VALUES( 
				@SettingsID,
				@StartingAddress,
				@EndingAddress,
				@BannedDate
			)

			SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
			IF( @ERROR <> 0 )
			BEGIN
				RAISERROR( 'An error occurred while trying to create a new cs_BannedNetwork record.', 16, 1 )
				RETURN @ERROR
			END
			ELSE
			BEGIN
				SET @BannedNetworkID = @ID
				RETURN 0
			END
		END
	END
	ELSE IF( @ActionType = 1 )
	BEGIN
		-- UPDATE
		IF NOT EXISTS( SELECT * FROM cs_BannedNetworks WHERE SettingsID = @SettingsID and BannedNetworkID = @BannedNetworkID )
			exec cs_BannedNetwork_CreateUpdateDelete @BannedNetworkID OUTPUT, @SettingsID, @StartingAddress, @EndingAddress, @BannedDate, 0 
		ELSE
		BEGIN
			UPDATE cs_BannedNetworks SET
				SettingsID		= @SettingsID,
				StartingAddress	= @StartingAddress,
				EndingAddress	= @EndingAddress,
				BannedDate		= @BannedDate
			WHERE
					SettingsID		= @SettingsID 
				AND BannedNetworkID = @BannedNetworkID
				
			SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
			IF( @ERROR <> 0 )
			BEGIN
				RAISERROR('Could not update the cs_BannedNetworks record for SettingsID %d and BannedNetworkID of %d', 16, 1, @SettingsID, @BannedNetworkID )
				RETURN @ERROR
			END
			ELSE
			BEGIN
				RETURN 0				
			END
		END
	END
	ELSE IF( @ActionType = 2 )
	BEGIN
		-- DELETE
		DELETE cs_BannedNetworks
		WHERE SettingsID		= @SettingsID
			AND BannedNetworkID = @BannedNetworkID
			
		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_BannedNetwork_CreateUpdateDelete]  TO [public]
GO