MySQL to SQL Server SP or Entity Framework

Question

I am having trouble converting this MySQL function into SQl Server or even to C# code for entity framework.

I will use this code with GEO Lite database so I can get the location information on an IP address.

BEGIN
    DECLARE loc_id INT;

    SELECT geoname_id
    INTO loc_id
    FROM ip_blocks
    WHERE ip_to >= INET_ATON(TRIM(ip))
    ORDER BY ip_to LIMIT 1;

    RETURN IFNULL(loc_id, 0);
END

I tried creating this SQL Server function to replace the mysql function INET_ATON

CREATE FUNCTION dbo.ipStringToInt (@ip CHAR(15))
RETURNS INT
AS
BEGIN
    DECLARE @rv INT
        ,@o1 INT
        ,@o2 INT
        ,@o3 INT
        ,@o4 INT
        ,@base INT

    SELECT @o1 = CONVERT(INT, PARSENAME(@ip, 4))
        ,@o2 = CONVERT(INT, PARSENAME(@ip, 3))
        ,@o3 = CONVERT(INT, PARSENAME(@ip, 2))
        ,@o4 = CONVERT(INT, PARSENAME(@ip, 1))

    IF (
            @o1 BETWEEN 0
                AND 255
            )
        AND (
            @o2 BETWEEN 0
                AND 255
            )
        AND (
            @o3 BETWEEN 0
                AND 255
            )
        AND (
            @o4 BETWEEN 0
                AND 255
            )
    BEGIN
        SELECT @base = CASE 
                WHEN @o1 < 128
                    THEN (@o1 * 16777216)
                ELSE - (256 - @o1) * 16777216
                END

        SET @rv = @base + (@o2 * 65536) + (@o3 * 256) + (@o4)
    END
    ELSE
        SET @rv = - 1

    RETURN @rv
END
GO

And also convert the MySQL function to this version of SQL Server:

CREATE FUNCTION IP2Location (@ip VARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @loc_id INT

    SET @loc_id = (
            SELECT geoname_id
            FROM ip_blocks
            WHERE ip_to >= dbo.ipStringToInt(TRIM(@ip))
            ORDER BY ip_to LIMIT 1
            )

    RETURN IFNULL(@loc_id, 0);
END
GO

But I am getting errors like Trim is not recognized built-in function name which is strange because it is.

And getting an error on @loc_id which says it is expecting (

Would appreciate the help

Thank you

EDIT #1: My function looks like this now:

CREATE FUNCTION IP2Location (@ip varchar(50)) RETURNS INTEGER
AS
BEGIN
DECLARE @loc_id INTEGER 

SELECT top 1 geoname_id INTO @loc_id FROM ip_blocks WHERE ip_to >= dbo.ipStringToInt(LTRIM(RTRIM(@ip))) ORDER BY ip_to

RETURN isnull(@loc_id, 0);
END
go

Answer 1

For error:

Trim is not recognized built-in function name

TRIM() is only available starting in SQL Server 2017.

If you are not using that version, use LTRIM(RTRIM(@ip)).

---

For error:

And getting an error on @loc_id which says it is expecting (

Instead of IFNULL(), I think what you are looking for is ISNULL()

---

For error:

Invalid use of a side-effecting operator SELECT within a function

Instead of:

SET @loc_id = (
        SELECT geoname_id
        FROM ip_blocks
        WHERE ip_to >= dbo.ipStringToInt(TRIM(@ip))
        ORDER BY ip_to LIMIT 1
        )

Or:

SELECT TOP 1 geoname_id
INTO @loc_id
FROM ip_blocks
WHERE ip_to >= dbo.ipStringToInt(LTRIM(RTRIM(@ip)))
ORDER BY ip_to

Use instead:

SET @loc_id = (
        SELECT TOP 1 geoname_id
        FROM ip_blocks
        WHERE ip_to >= dbo.ipStringToInt(LTRIM(RTRIM(@ip)))
        ORDER BY ip_to
        )