title: Convert procedure to UDF
body: <p>i want to convert this procedure into UDF any suggestion. i have tried alot of things but can't access the required result</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spLocationforSMS]  
  @latt varchar(150)=null,
  @lont varchar(150)
   AS
  BEGIN
  IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
  CREATE TABLE #xml ( yourXML XML )
  
  DECLARE @URL VARCHAR(8000) 
  DECLARE @QS varchar(50)
  SELECT @QS = '&amp;date='+convert(varchar(25),getdate(),126)
  SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?
  latlng='+@latt+','+@lont+'&amp;sensor=false'  + @QS
  
   DECLARE @Response varchar(8000)
   DECLARE @XML xml
   DECLARE @Obj int 
   DECLARE @Result int 
   DECLARE @HTTPStatus int 
   DECLARE @ErrorMsg varchar(MAX)
   EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT
   EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
   EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
  EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
  EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 
  INSERT #xml ( yourXML )
  EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 
  SELECT  yourXML.value('(//GeocodeResponse/result/formatted_address)[1]','VARCHAR(MAX)') from #xml
  </code></pre>