-
-- Enable CLR integration (啟動 SQL Server 2005 CLR 功能)
-
sp_configure 'clr enabled', 1
-
GO
-
RECONFIGURE
-
GO
-
-- Register Assembly (註冊組件)
-
USE master
-
CREATE ASSEMBLY IPUtility
-
FROM 'C:\TMP\IPUtility.dll' -- 請依照 dll 放置位置填寫
-
WITH PERMISSION_SET = Safe
-
GO
-
---- Drop assembly
-
--DROP ASSEMBLY IPUtility
-
--GO
-
-- Create the managed user-defined function
-
USE master
-
GO
-
CREATE FUNCTION dbo.IsInRange
-
(
-
@IP nvarchar( 15 ), @startIP nvarchar( 15 ), @endIP nvarchar( 15 ) -- 需要三參數: 比對 IP, 範圍起始 IP , 範圍結束 IP
-
)
-
RETURNS int
-
AS EXTERNAL NAME IPUtility.IPUtility.IsInRange
-
GO
-
-- Test Run (測試 Function)
-
DECLARE @IPTable TABLE (IP nvarchar( 15 ) )
-
INSERT INTO @IPTable VALUES ( N'172.16.57.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.58.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.59.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.60.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.61.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.62.103' )
-
SELECT IP
-
FROM @IPTable
-
WHERE dbo.IsInRange(IP, '172.16.58.103', '172.16.61.109' ) = 1
-
GO