ALTER 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
/**//*这个和禁止地址差不多,就是把地址扩大到网络,就是从某IP开始到某IP结束这段地址*/
-- CREATE
IF @ActionType = 0
BEGIN
IF EXISTS( SELECT * FROM cs_BannedNetworks where SettingsID = @SettingsID and BannedNetworkID = @BannedNetworkID )
/**//*当活动类型为0的情况下,如果存在此地址于封闭表中,则执行活动类型为1的存储过程*/
exec cs_BannedNetworks_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 --成功返回0
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 )
/**//*如果活动类型为1,但是表中不存在此记录则返回执行插入数据的上一段存储过程进行添加数据,然后再返回这里
此时就存在此纪录,转到ELSE块.更新*/
exec cs_BannedNetworks_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 --成功返回0
RETURN 0
END
END
END
ELSE IF( @ActionType = 2 )
BEGIN
-- DELETE
/**//*如果活动类型为2(因为上面的活动类型在0和1之间,
所以可以判断在2的情况下记录肯定存在且被更新)*/
DELETE cs_BannedNetworks
WHERE SettingsID = @SettingsID
AND BannedNetworkID = @BannedNetworkID
/**//*删除记录*/
SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
IF( @ROWCOUNT = 1 ) --成功返回0
RETURN 0
ELSE
RETURN 1
END
ELSE
BEGIN /**//*如果活动类型不在0和2之间,则创建错误信息,返回1*/
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
ALTER Procedure [dbo].[cs_BannedAddresses_Get]
@SettingsID int
AS
/**//*查询被封闭的地址*/
SELECT
SettingsID
, NetworkAddress
, BannedDate
FROM cs_BannedAddresses
WHERE SettingsID = @SettingsID
GO
@SettingsID int
AS
/**//*查询被封闭的地址*/
SELECT
SettingsID
, NetworkAddress
, BannedDate
FROM cs_BannedAddresses
WHERE SettingsID = @SettingsID
GO
ALTER Procedure [dbo].[cs_BannedNetworks_Get]
@SettingsID int
AS
/**//*查询被禁止的网络*/
SELECT SettingsID
, BannedNetworkID
, StartingAddress
, EndingAddress
, BannedDate
FROM cs_BannedNetworks
WHERE SettingsID = @SettingsID
GO