MS-SQL: Statement zum Berechnen von IP-Ranges

--Funktion
CREATE FUNCTION dbo.test_convert_ipv4(@ipv4cidr_decimal VARCHAR(18))
RETURNS @t TABLE (
	ipv4_binary VARCHAR(32),
	ipv4_binary_net VARCHAR(32),
	ipv4_binary_host VARCHAR(32),
	ipv4cidr_decimal_net SMALLINT
)
AS
BEGIN
	DECLARE	@ipv4cidr_decimal_net SMALLINT,
			@ipv4_decimal VARCHAR(15)

	IF		@ipv4cidr_decimal LIKE '%/%'  
	BEGIN
		SET		@ipv4_decimal = left(@ipv4cidr_decimal,charindex('/',@ipv4cidr_decimal)-1) + '.'  
		SET		@ipv4cidr_decimal_net = convert(SMALLINT,right(@ipv4cidr_decimal,charindex('/',reverse(@ipv4cidr_decimal))-1))  
	END
	ELSE
	BEGIN
		SET		@ipv4_decimal = @ipv4cidr_decimal + '.'  
	END;

	WITH cte(ipv4,octet,number) AS (
		SELECT	right(@ipv4_decimal,datalength(@ipv4_decimal)-charindex('.',@ipv4_decimal)),  
				left(@ipv4_decimal,charindex('.',@ipv4_decimal)-1),  
				1
		UNION ALL
		SELECT	right(ipv4,datalength(ipv4)-charindex('.',ipv4)),  
				left(ipv4,charindex('.',ipv4)-1),  
				number + 1
		FROM	cte
		WHERE	number <= 3
		)
	INSERT INTO @t(ipv4_binary)
	SELECT	stuff((	SELECT	( CASE convert(INT,octet) & 128 WHEN 128 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 64 WHEN 64 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 32 WHEN 32 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 16 WHEN 16 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 8 WHEN 8 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 4 WHEN 4 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 2 WHEN 2 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 1 WHEN 1 THEN '1' ELSE '0' END )  
					FROM	cte
					ORDER BY number
					FOR XML PATH (''))  
					,1,1,'')  

	IF		@ipv4cidr_decimal_net IS NOT NULL
	BEGIN
		UPDATE	@t
		SET		ipv4_binary_net = left(ipv4_binary,@ipv4cidr_decimal_net),
				ipv4_binary_host = right(ipv4_binary,32-@ipv4cidr_decimal_net),
				ipv4cidr_decimal_net = @ipv4cidr_decimal_net
	END

	RETURN;
END;

--Testdaten und Abfrage
WITH Netzwerkbereichsdefintionen(IP_Bereich,Name) AS (
	SELECT	'192.168.0.0/24','Standardnetz' UNION ALL  
	SELECT	'10.27.0.0/16','Servernetz' UNION ALL  
	SELECT	'172.16.4.0/24','DMZ'  
	), Geräteliste(Gerätename,IP_Adresse) AS (
	SELECT	'Server1','192.168.0.16' UNION ALL  
	SELECT	'Server2','10.27.0.12' UNION ALL  
	SELECT	'Server3','172.16.4.4' UNION ALL  
	SELECT	'Server4','10.27.0.13'  
	), Netzwerkbereichsdefintionen_erweitert(IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net) AS (
	SELECT	IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net
	FROM	Netzwerkbereichsdefintionen
	CROSS APPLY dbo.test_convert_ipv4(IP_Bereich)
	), Geräteliste_erweitert(IP_Adresse,Gerätename,ipv4_binary) AS (
	SELECT	IP_Adresse,Gerätename,ipv4_binary
	FROM	Geräteliste
	CROSS APPLY dbo.test_convert_ipv4(IP_Adresse)
	)
SELECT	*
FROM	 Netzwerkbereichsdefintionen_erweitert nets
LEFT JOIN Geräteliste_erweitert hosts
ON		nets.ipv4_binary_net = left(hosts.ipv4_binary,nets.ipv4cidr_decimal_net)