How to invoke webservice from SQL Server stored procedure -

i know how use web service sql server stored procedure. have table in columns specify city , state of user. want pass address parameter google geocoding api web service , latitude , longitude.

i have made use of geocoding api in c#. now, want use in stored procedure.

can 1 please suggest how done? or please provide me links?

any appreciated!!

thanks.

for this, don't need full web service implementation. can use sqlclr (sql server's .net integration) submit request url, response in xml (unless can find json library work without being set unsafe), , parse response.

look @ following msdn pages:

• httpwebrequest
• httpwebresponse
• xmldocument
• to escape address:
  • if using sql server 2005, 2008, or 2008 r2, use uri.escapedatastring available prior .net framework v4.5
  • if using sql server 2012, 2014, or newer, can use either uri.escapedatastring or, if server has been updated @ least .net framework v4.5, can alternatively use webutility.urlencode

according google geocoding api documentation, api uri should formatted following:

https://maps.googleapis.com/maps/api/geocode/xml?address={escapedaddress}&key={api_key} 

just submit 2 variables substituted proper values via httpwebrequest, call httpwebrequest.getresponse, call httpwebresponse.getresponsestream. , not forget call close , dispose methods of httpwebresponse (or instantiate in using block)!!

additional notes:

• if not done, have enable (one time) "clr integration" @ server level: enabling clr integration
• do not take easy road , set database trustworthy on. sign assembly password, create asymmetric key in master database pointing signed dll, create login asymmetric key, , grant login unsafe assembly permission. can set assembly with permission_set = external_access.
• do not use sp_oa* procedures advocated user3469363. ole automation procedures have been deprecated since sql server 2005 , (hopefully) removed someday (hopefully soon). less efficient , less secure sqlclr.