Checking IP Against CIDR Netmask with T-SQL

I’ve been in the process of improving the functionality in ASLB, one of the improvements I have been working on adding is functionality for ASLB to be smart enough to understand geo-location in a large network.

However in doing so I went through many iterations on how to identify which network a user is in for picking the server priority. The result was to create a network table and a scalar function which returned a bit if the provided IP address was in the supplied sub network. Below is the function I used to accomplish this. With a little more work I may modify the function to accept the netmask in CIDR notation instead of two separate columns.

-- Author: Anthony Mattas 
-- Create date: 1-28-2012 
-- Description: This function takes an IP address, CIDR network, and CIDR netmask 
-- (i.e. 192.168.0.0/16) and checks to see if the IP address is in the 
-- network 
-- =================================================================

CREATE FUNCTION [dbo].[udf_CheckNetmask] ( 
     @Address VARCHAR(16), 
     @Network VARCHAR(16), 
     @Netmask INT 
) 
RETURNS BIT 
AS 
BEGIN 
     DECLARE @Octet1 INT 
     DECLARE @Octet2 INT 
     DECLARE @Octet3 INT 
     DECLARE @Octet4 INT 

     DECLARE @BAddress BINARY(4) 
     DECLARE @BNetwork BINARY(4) 

     DECLARE @Return BIT 

     SELECT @BAddress = 
          CAST( 
               CAST( 
                    PARSENAME( @Address, 4 ) AS INTEGER
               ) AS BINARY(1)
          ) + 
          CAST( 
               CAST( 
                    PARSENAME( @Address, 3 ) AS INTEGER
               ) AS BINARY(1)
          ) + 
          CAST( 
               CAST( 
                    PARSENAME( @Address, 2 ) AS INTEGER
               ) AS BINARY(1)
          ) + 
          CAST( 
               CAST( 
                    PARSENAME( @Address, 1 ) AS INTEGER
               ) AS BINARY(1)
          ) 

SELECT @BNetwork = 
          CAST( 
               CAST( 
                    PARSENAME( @Network, 4 ) AS INTEGER
               ) AS BINARY(1)
          ) + 
          CAST(
               CAST( 
                    PARSENAME( @Network, 3 ) AS INTEGER
               ) AS BINARY(1)
          ) + 
          CAST( 
               CAST( 
                    PARSENAME( @Network, 2 ) AS INTEGER
               ) AS BINARY(1)
          ) + 
          CAST( 
               CAST( 
                    PARSENAME( @Network, 1 ) AS INTEGER
               ) AS BINARY(1)
          ) 

SELECT @Return = 
     CASE 
          WHEN 0 = (
               cast(@BAddress as bigint) ^ 
               cast(@BNetwork as bigint)
          ) & 
          ~(
               power(
                    CAST(2 AS bigint), 
                    32 - @Netmask
               ) - 1
          ) THEN 1
          ELSE 0 
     END 

RETURN @Return 
END 
GO