CREATE FUNCTION for "Geocode" failed because T-SQL and CLR types for return value do not match

Question

I am trying to geocode addressed from a table and found a good example here: How to store geocoded address information into the database

or this code

using System.Net;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.Web;
using System.Data.SqlClient;
using Microsoft.SqlServer.Types;
using System.ServiceModel.Web;

namespace ProSQLSpatial
{
public partial class GoogleMaps
{
    /// <summary>
    ///
    /// </summary>
    /// <param name="address"></param>
    /// <returns></returns>
    public static GeoResponse GetGeoCodedResults(string address)
    {
        string url = string.Format(
                "http://maps.google.com/maps/api/geocode/json?address={0}&region=dk&sensor=false"

                );
        var request = (HttpWebRequest)HttpWebRequest.Create(url);
        request.Headers.Add(HttpRequestHeader.AcceptEncoding, "gzip,deflate");
        request.AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;
        DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(GeoResponse));
        var res = (GeoResponse)serializer.ReadObject(request.GetResponse().GetResponseStream());
        return res;
    }
}

[DataContract]
public class GeoResponse
{
    [DataMember(Name = "status")]
    public char Status { get; set; }

    [DataMember(Name = "results")]
    public CResult[] Results { get; set; }

    [DataContract]
    public class CResult
    {
        [DataMember(Name = "geometry")]
        public CGeometry Geometry { get; set; }

        [DataContract]
        public class CGeometry
        {
            [DataMember(Name = "location")]
            public CLocation Location { get; set; }

            [DataContract]
            public class CLocation
            {
                [DataMember(Name = "lat")]
                public double  Lat { get; set; }

                [DataMember(Name = "lng")]
                public double Lng { get; set; }
            }
        }
    }

    public GeoResponse()
    { }
}

I created the assembly in SQL Server and tried to create a function based on the assembly by:

CREATE FUNCTION dbo.Geocode
    (@Address nvarchar(4000)) 
RETURNS nvarchar(4000)
AS EXTERNAL NAME [test2].[ProSQLSpatial.GoogleMaps].[GetGeoCodedResults];
GO

but I get the error message

Msg 6551, Level 16, State 2, Procedure Geocode, Line 1
CREATE FUNCTION for "Geocode" failed because T-SQL and CLR types for return value do not match.

I have done searching and I can't figure it out. I have tried changing data types, but still get the same message. Any help would be greatly would make my day. Thanks

Answer 1

This is a working example suited for your case:

The C# code (removed the web-stuff for simplicity)

using Microsoft.SqlServer.Server;
using System.Collections;
using System;

namespace ProSQLSpatial
{
    public partial class GoogleMaps
    {
        [SqlFunction(FillRowMethodName="FillRow")]
        public static IEnumerable GetGeoCodedResults(string address)
        {
            if (address == "test1")
                yield return new GeoResponse { lat = 0.1d, lng = 0.2d };
            else
                yield return new GeoResponse { lat = 0.2d, lng = 0.1d };
        }

        public static void FillRow(Object obj, out double lat, out double lng)
        {
            var entry = (GeoResponse)obj;
            lat = entry.lat;
            lng = entry.lng;
        }
    }

    public class GeoResponse
    {
        public double lat;
        public double lng;
    }
}

And here we have the SQLServer commands to register the dll and function:

CREATE ASSEMBLY TestLib FROM 'C:\Temp\TestLib.dll'
go

CREATE FUNCTION GeoCode(@address nvarchar(4000))
RETURNS TABLE (lat float, lng float)
EXTERNAL NAME [TestLib].[ProSQLSpatial.GoogleMaps].[GetGeoCodedResults]
GO

Some testing code to see if it does the trick:

create table addresses (id int identity(1, 1) primary key, [address] nvarchar(4000))
go

insert into addresses([address]) values ('test1'), ('test2')
go

select * 
from addresses x
cross apply dbo.GeoCode(x.address)

select * from dbo.GeoCode('test1')

Seems to do the trick over here.