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.
Laxmidhar SahooPosted Jan 3, 2019, 10:46 PM
Gary HalePosted Jan 3, 2019, 9:38 AM