CREATE    FUNCTION dbo.aif_calcipnwaddr

/*    Returns the netork address for the specified IP address/network mask
    if valid otherwise returns an empty string

    2005-08-04    George Curran, Computer Associates
            Add grant execute to "aiadmin" role
    2004-06-17     George Curran, Computer Associates
*/

(
--    IP address
    @strIp nvarchar(16) = NULL,

--    Network mask
    @strNwMask nvarchar(16) = NULL
)

RETURNS nvarchar(16)
AS
BEGIN
--    Declare and initialize local variables
    DECLARE @intPos int
    DECLARE @intLen int
    DECLARE @strIpOct nvarchar(16)
    DECLARE @strNwOct nvarchar(16)
    DECLARE    @strNwAddr nvarchar(16)

    SET    @strNwAddr = ''

--    Verify IP address and network mask complies with expect format
    IF    (@strIp NOT LIKE '%.%.%.%')
    OR    (@strNwMask NOT LIKE '%.%.%.%')
        RETURN ''

    WHILE    (@strIp <> '')
    AND    (@strNwMask <> '')
    BEGIN
    --    Get IP octet
        SET    @intPos = CHARINDEX('.', @strIp)
        IF    (@intPos > 0)
        BEGIN
            SET @intLen = @intPos - 1
            SET @strIpOct = LEFT(@strIp, @intLen)
            SET @intLen = LEN(@strIp) - @intPos
            SET @strIp = RIGHT(@strIp, @intLen)
        END
        ELSE
        BEGIN
            SET @strIpOct = @strIp
            SET @strIp = ''
        END

    --    Validate octet format
        IF    (@strIpOct NOT LIKE '[0-9]')
        AND    (@strIpOct NOT LIKE '[0-9][0-9]')
        AND    (@strIpOct NOT LIKE '[0-9][0-9][0-9]')
            RETURN ''

    --    Validate octet value
        IF    (@strIpOct > 254)
            RETURN ''
    
    --    Get network mask octet
        SET    @intPos = CHARINDEX('.', @strNwMask)
        IF    (@intPos > 0)
        BEGIN
            SET @intLen = @intPos - 1
            SET @strNwOct = LEFT(@strNwMask, @intLen)
            SET @intLen = LEN(@strNwMask) - @intPos
            SET @strNwMask = RIGHT(@strNwMask, @intLen)
        END
        ELSE
        BEGIN
            SET @strNwOct = @strNwMask
            SET @strNwMask = ''
        END

    --    Validate octet format
        IF    (@strNwOct NOT LIKE '[0-9]')
        AND    (@strNwOct NOT LIKE '[0-9][0-9]')
        AND    (@strNwOct NOT LIKE '[0-9][0-9][0-9]')
            RETURN ''

    --    Validate octet value
        IF    (@strNwOct > 255)
            RETURN ''

    --    Update network address
        SET    @strNwAddr = @strNwAddr + CAST((CAST(@strIpOct AS int) & CAST(@strNwOct AS int)) AS nvarchar(3))
        IF    (@strIp <> '')
        AND    (@strNwMask <> '')
            SET @strNwAddr = @strNwAddr + '.'
    END
    
    RETURN @strNwAddr
END

GO
GRANT EXECUTE ON  [dbo].[aif_calcipnwaddr] TO [aiadmin]
GO