Forum Topics : Development / Using MS-SQL
-- My MS-SQL Codes here ;)
-- IPData : http://www.ip-to-country.com
-- Code by RaonHaje 2005-09-13
-- ex) EXEC spGetIPtoCountry '130.60.58.2'
CREATE TABLE IPtoCountry
(
IPFrom BIGINT NOT NULL,
IPTo BIGINT NOT NULL,
CountryCode2 CHAR(2),
CountryCode3 CHAR(3),
CountryName] VARCHAR(50) NOT NULL
) ON PRIMARY
GO
CREATE CLUSTERED INDEX IX_IPtoCountry_IPRange ON (IPFrom, IPTo) ON PRIMARY
GO
ALTER FUNCTION inet_aton (@IP VARCHAR(15))
RETURNS BIGINT
AS
BEGIN
DECLARE @A BIGINT, @B BIGINT, @C BIGINT, @D BIGINT
DECLARE @iBegin INT, @iEnd INT
SELECT @iBegin=1
SELECT @iEnd=CHARINDEX('.', @IP)
SELECT @A=CAST(SUBSTRING(@IP, @iBegin, @iEnd-@iBegin) AS BIGINT)
SELECT @iBegin=@iEnd+1
SELECT @iEnd=CHARINDEX('.', @IP, @iBegin)
SELECT @B=CAST(SUBSTRING(@IP, @iBegin, @iEnd-@iBegin) AS BIGINT)
SELECT @iBegin=@iEnd+1
SELECT @iEnd=CHARINDEX('.', @IP, @iBegin)
SELECT @C=CAST(SUBSTRING(@IP, @iBegin, @iEnd-@iBegin) AS BIGINT)
SELECT @iBegin=@iEnd+1
SELECT @iEnd=CHARINDEX('.', @IP, @iBegin)
SELECT @D=CAST(SUBSTRING(@IP, @iBegin, 15) AS BIGINT)
DECLARE @IPNumber BIGINT
SELECT @IPNumber=@A*16777216+@B*65536+@C*256+@D
RETURN @IPNumber
END
ALTER PROCEDURE spGetIPtoCountry
@IP VARCHAR(15)
AS
BEGIN
DECLARE @IPNumber BIGINT
SELECT @IPNumber=TestDB.dbo.inet_aton(@IP)
SELECT * FROM IPToCountry WHERE IPFrom <= @IPNumber AND @IPNumber <= IPTo
END
