SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE Insert_Range
	-- Add the parameters for the stored procedure here
	@range_start_new nvarchar (15),
	@range_end_new nvarchar (15)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF EXISTS 
	( 
		SELECT [IP_Ranges].[Range_Start], [IP_Ranges].[Range_End] FROM [dbo].[IP_Ranges]
		WHERE 
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_Start], 2)) <> CONVERT (INT, PARSENAME (@range_start_new, 2))) AND
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_End], 2)) <> CONVERT (INT, PARSENAME (@range_end_new, 2)))
	)
			
	BEGIN
		INSERT INTO [dbo].[IP_Ranges] 
		(
			[IP_Ranges].[Range_Start],
			[IP_Ranges].[Range_End]
		)
		VALUES
		(
			@range_start_new,
			@range_end_new
		)

		RETURN 1;
	END
		
	ELSE
	BEGIN
		RETURN -1;
	END 
END
GO

DECLARE @range_start_new nvarchar (15),	@range_end_new nvarchar (15)
SET @range_start_new = '127.58.53.0'
SET @range_end_new = '127.58.53.255'

SELECT [IP_Ranges].[Range_Start], [IP_Ranges].[Range_End] FROM [dbo].[IP_Ranges]
WHERE 
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_Start], 2)) <> CONVERT (INT, PARSENAME (@range_start_new, 2))) AND
			(CONVERT (INT, PARSENAME ([IP_Ranges].[Range_End], 2)) <> CONVERT (INT, PARSENAME (@range_end_new, 2)))

if not exists (
	select 0 from dbo.IPRanges where parsename(@range_start_new, 2) between parsename(Range_Start, 2) and parsename(Range_End, 2)
	)
	and not exists (
	select 0 from dbo.IPRanges where parsename(@range_end_new, 2) between parsename(Range_Start, 2) and parsename(Range_End, 2)
	)
	-- Insert here

CREATE TABLE [dbo].[SubNet] (
	[SubNet]	Binary(4)	NOT NULL
,	[Mask]		TinyInt		NOT NULL
,	CONSTRAINT [PK_SubNet] PRIMARY KEY (
		[SubNet]
	,	[Mask]
	)
,	CONSTRAINT [UQ_SubNet] UNIQUE (
		[Mask]
	,	[SubNet]
	)
)
GO
CREATE VIEW [dbo].[vwSubNet] AS
SELECT	S.*
,	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,1,1))) + '.'
+	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,2,1))) + '.'
+	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,3,1))) + '.'
+	Convert(VarChar(3),Convert(TinyInt,SubString(S.SubNet,4,1)))	AS SubNetIP
-- TODO: Host Address Range, Broadcast Address, Wildcard Mask etc.
FROM	dbo.SubNet	S
GO
CREATE VIEW [dbo].[SubNetMask] AS
SELECT * FROM (VALUES
 (32,Convert(Int,0xFFFFFFFF))
,(31,Convert(Int,0xFFFFFFFE))
,(30,Convert(Int,0xFFFFFFFC))
,(29,Convert(Int,0xFFFFFFF8))
,(28,Convert(Int,0xFFFFFFF0))
,(27,Convert(Int,0xFFFFFFE0))
,(26,Convert(Int,0xFFFFFFC0))
,(25,Convert(Int,0xFFFFFF80))
,(24,Convert(Int,0xFFFFFF00))
,(23,Convert(Int,0xFFFFFE00))
,(22,Convert(Int,0xFFFFFC00))
,(21,Convert(Int,0xFFFFF800))
,(20,Convert(Int,0xFFFFF000))
,(19,Convert(Int,0xFFFFE000))
,(18,Convert(Int,0xFFFFC000))
,(17,Convert(Int,0xFFFF8000))
,(16,Convert(Int,0xFFFF0000))
) M (Bits,Mask)
GO
CREATE FUNCTION [dbo].[fnIPSubNet] (
	@IP	VarChar(15)
) RETURNS TABLE AS RETURN
SELECT	Convert(Binary(4),Convert(Int,
	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,4)))
+	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,3)))
+	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,2)))
+	Convert(VarBinary,Convert(TinyInt,ParseName(@IP,1)))
&	M.Mask))	AS SubNet
,	M.Bits		AS Mask
FROM	dbo.SubNetMask	M
GO

CREATE TRIGGER [dbo].[trSubNetCheck] ON [dbo].[SubNet]
AFTER INSERT,UPDATE AS BEGIN
	SET NOCOUNT ON;

	IF Exists(	-- Надсети, перебором SEEK
		SELECT	*
		FROM	Inserted	I
		JOIN	dbo.SubNetMask	M ON M.Bits	< I.Mask
		JOIN	dbo.SubNet	T ON T.Mask	= M.Bits
					 AND T.SubNet	= I.SubNet & M.Mask
	) OR Exists(	-- Подсети, по диапазону
		SELECT	*
		FROM	Inserted	I
		JOIN	dbo.SubNetMask	M ON M.Bits	= I.Mask
		JOIN	dbo.SubNet	T ON T.SubNet	BETWEEN I.SubNet AND I.SubNet | ~M.Mask
					 AND T.Mask	> I.Mask
	) BEGIN
		ROLLBACK;
		RAISERROR('SubNet range intersect',16,1);
		RETURN;
	END
END
GO
CREATE PROCEDURE [dbo].[spSubNetAdd]
	@IP	VarChar(15)
,	@Mask	TinyInt
AS BEGIN
	SET NOCOUNT ON;

	INSERT	dbo.SubNet	( SubNet,  Mask)
	SELECT			S.SubNet,S.Mask
	FROM	dbo.fnIPSubNet(@IP)	S
	WHERE	S.Mask = @Mask
	IF (@@RowCount != 1)
		RAISERROR('Wrong Subnet',16,1)
END
GO

EXEC dbo.spSubNetAdd '195.58.53.0',24
EXEC dbo.spSubNetAdd '195.58.52.0',23	-- SubNet range intersect

DECLARE	@@IP VarChar(15) = '195.58.53.3'
SELECT	TOP(1) S.*
FROM	dbo.fnIPSubNet(@@IP)	I
JOIN	dbo.vwSubNet		S ON S.SubNet	= I.SubNet
				 AND S.Mask	= I.Mask
ORDER BY I.Mask DESC