This is a Proof of Concept
CLR Funtion:
CREATE FUNCTION [dbo].[fnAddressCoordinates](@addressLine [NVARCHAR](MAX), @city [NVARCHAR](100), @state [NVARCHAR](2), @zip [NVARCHAR](15))
RETURNS TABLE (
[address] [NVARCHAR](MAX) NULL,
[city] [NVARCHAR](MAX) NULL,
[state] [NVARCHAR](MAX) NULL,
[zip] [NVARCHAR](MAX) NULL,
[lat] [DECIMAL](18, 6) NULL,
[lon] [DECIMAL](18, 6) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GoogleGeocodeAPI].[GoogleGeocodeAPI.GoogleGeocodeAPI].[AddressCoordinates]
GO
c# Code Snipit
[SqlFunction(FillRowMethodName = "AddressCoordinates")]
public static System.Collections.IEnumerable AddressCoordinates(SqlString address, SqlString city, SqlString state, SqlString zip, SqlString Bla)
{
// Input
GeoCode Req = new GeoCode
{
address = address.ToString(),
city = city.ToString(),
state = state.ToString(),
zip = zip.ToString(),
latitude = 0,
longitude = 0
};
...
// Results
ArrayList resultCollection = new ArrayList();
resultCollection.Add(Req);
return resultCollection;
Error Message
create function failed because the parameter count for the fillrow method should be one more than the sql declaration for the table valued clr function.