DROP FUNCTION CalculateIP;
GO
CREATE FUNCTION CalculateIP (
@varIp AS varchar(20)
) RETURNS bigint
BEGIN
Declare @IP varchar(20);
Declare @multi1 bigint;
Declare @multi2 bigint;
Declare @multi3 bigint;
Declare @value bigint;
SET @multi1 = 16777216;
SET @multi2 = 65536;
SET @multi3 = 256;
SELECT @IP = @varIp;
SELECT @value = (@multi1 * cast(substring(@IP, 1, charindex('.', @IP) - 1) as int));
SELECT @value = (@value + @multi2 * cast(substring(@IP, charindex('.', @IP) + 1, charindex('.', @IP, charindex('.', @IP) + 1) - charindex('.', @IP) - 1) as int));
SELECT @value = (@value + @multi3 * cast(reverse(substring(reverse(@IP), charindex('.', reverse(@IP)) + 1, charindex('.', reverse(@IP), charindex('.', reverse(@IP)) + 1) - charindex('.', reverse(@IP)) - 1)) as int));
SELECT @value = (@value + cast(reverse(substring(reverse(@IP), 1, charindex('.', reverse(@IP)) - 1)) as int));
RETURN @value;
END
GO
DROP PROC getCheckData;
GO
CREATE PROC getCheckData @IP varchar(20)
AS
DECLARE @startIp bigint;
DECLARE @endIp bigint;
DECLARE @locId bigint;
DECLARE @country varchar(20);
DECLARE @city varchar(20);
DECLARE @region varchar(125);
DECLARE @country_id int;
DECLARE @city_id int;
DECLARE @region_id int;
DECLARE @lat varchar(20);
DECLARE @long varchar(20);
DECLARE @checkpoint varchar(255);
DECLARE @checkpoint_id varchar(255);

CREATE TABLE #tempData (
startIpnum bigint,
endIpnum bigint,
locId bigint);
SELECT @startIp = startIpnum FROM GeoCountry WHERE dbo.CalculateIP(@IP) BETWEEN GeoCountry.startIpnum AND GeoCountry.endIpnum;
SELECT @endIp = endIpnum FROM GeoCountry WHERE dbo.CalculateIP(@IP) BETWEEN GeoCountry.startIpnum AND GeoCountry.endIpnum
INSERT INTO #tempData (startIpnum, endIpnum, locId) SELECT startIpnum, endIpnum, locId FROM GeoBlock WHERE startIpnum >= @startIp AND endIpnum <= @endIp;
SELECT @locid = GeoLocation.locId, @country = GeoLocation.country, @city = GeoLocation.city, @lat = GeoLocation.latitude, @long = GeoLocation.longitude,@region = (
SELECT GeoRegion.title FROM GeoRegion,GeoLocation,#tempData WHERE #tempData.locId = GeoLocation.locId AND dbo.CalculateIP(@IP) BETWEEN #tempData.startIpnum AND #tempData.endIpnum
AND (GeoLocation.region = GeoRegion.region AND GeoLocation.country = GeoRegion.country)
)
FROM #tempData, GeoLocation
WHERE #tempData.locId = GeoLocation.locId AND dbo.CalculateIP(@IP) BETWEEN #tempData.startIpnum AND #tempData.endIpnum
DROP TABLE #tempData;
SELECT @city = city, @city_id = id FROM FrontAds WHERE city = @city;
SELECT @region = region, @region_id = id FROM FrontAds WHERE region = @region;
SELECT @country = country, @country_id = id FROM FrontAds WHERE country = @country;

SELECT @checkpoint = CASE
WHEN @city != ‘NODATA’ AND @city_id != ” THEN @city_id
WHEN @region != ‘NODATA’ AND @region_id != ” THEN @region_id
WHEN @country != ‘NODATA’ AND @country_id != ” THEN @country_id
END
SELECT
CASE
WHEN type = ’1′ THEN ‘‘ + ‘‘ + ‘‘
WHEN type = ’2′ THEN ‘‘ + ‘‘ + ‘‘
WHEN type = ’3′ THEN ‘‘ + ‘‘ + ‘‘
WHEN type = ’4′ THEN ‘‘ + ‘‘ + ‘‘
END
FROM FrontAds WHERE id = @checkpoint;

GO

EXEC getCheckData ’212.105.99.136′;