Provance.Report.ProvanceIpIsInRange.Install (Resource)

Element properties:

Source Code:

<Resource ID="Provance.Report.ProvanceIpIsInRange.Install" Accessibility="Public" FileName="ProvanceIpIsInRange.sql" HasNullStream="false"/>

File Content: ProvanceIpIsInRange.sql

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProvanceIpIsInRange]') and (OBJECTPROPERTY(id, N'IsScalarFunction') = 1 OR OBJECTPROPERTY(id, N'IsTableFunction') = 1)) 

 drop function [dbo].[ProvanceIpIsInRange] 

GO


CREATE FUNCTION [dbo].[ProvanceIpIsInRange]


(


	@IpRange VARCHAR(15),


	@SubnetRange VARCHAR(15),


	@IpAddress VARCHAR(15)


	


)


RETURNS BIT


AS


BEGIN


/*


This function returns 1 is the @IpAddress is in the Range of the @IpRange by aplying the @SubnetRange as a mask.


These column names reflect the names of the source data in Provance.


*/


 


RETURN	CASE


	WHEN	    dbo.ProvanceIpIsValid(@IpAddress)   = 1 


		and dbo.ProvanceIpIsValid(@IpRange)     = 1 


		and dbo.ProvanceIpIsValid(@SubnetRange) = 1 


		AND CAST(PARSENAME(@IpAddress, 4) AS TINYINT) & CAST(PARSENAME(@SubnetRange, 4) AS TINYINT) = CAST(PARSENAME(@IpRange, 4) AS TINYINT) & CAST(PARSENAME(@SubnetRange, 4) AS TINYINT)


		AND CAST(PARSENAME(@IpAddress, 3) AS TINYINT) & CAST(PARSENAME(@SubnetRange, 3) AS TINYINT) = CAST(PARSENAME(@IpRange, 3) AS TINYINT) & CAST(PARSENAME(@SubnetRange, 3) AS TINYINT)


		AND CAST(PARSENAME(@IpAddress, 2) AS TINYINT) & CAST(PARSENAME(@SubnetRange, 2) AS TINYINT) = CAST(PARSENAME(@IpRange, 2) AS TINYINT) & CAST(PARSENAME(@SubnetRange, 2) AS TINYINT)


		AND CAST(PARSENAME(@IpAddress, 1) AS TINYINT)	between CAST(PARSENAME(@IpRange, 1) AS TINYINT) & CAST(PARSENAME(@SubnetRange, 1) AS TINYINT) +1	


								and     CAST(PARSENAME(@IpRange, 1) AS TINYINT) | (CAST(PARSENAME(@SubnetRange, 1) AS TINYINT) ^ 0xFF) -1


	THEN	1


	ELSE	0


	END


END


GO