July 27, 2016 at 8:50 am
Hello, good day.
I'm wondering if you guys can help me out. I've been trying to figure out how to do or the best approach to get the result that I need.
I've the following SP that return the Lat and Lot from Google WebService
USE [LOCALDATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_BUSCAENDERECO] (
@Logradouro VARCHAR(500) = NULL,
@CEP VARCHAR(9) = NULL
)
AS BEGIN
SET NOCOUNT ON
SET @Logradouro = NULLIF(@Logradouro, '')
SET @CEP = NULLIF(@CEP, '')
IF (@Logradouro IS NULL AND @CEP IS NULL)
RETURN
DECLARE
@obj INT,
@Url VARCHAR(8000),
@resposta VARCHAR(8000),
@xml XML,
@endereco_busca VARCHAR(4000)
IF (@CEP IS NOT NULL AND @Logradouro IS NULL)
SET @endereco_busca = LEFT(@CEP, 5) + '-' + RIGHT(@CEP, 3) + ', Brasil'
ELSE
SET @endereco_busca = @Logradouro
SET @Url = 'http://maps.googleapis.com/maps/api/geocode/xml?address=' + @endereco_busca + '&sensor=false'
EXEC sys.sp_OACreate @progid = 'MSXML2.ServerXMLHTTP', @objecttoken = @obj OUT, @context = 1
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false
EXEC sys.sp_OAMethod @obj, 'send'
EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
EXEC sys.sp_OADestroy @obj
SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
IF (OBJECT_ID('tempdb..#XML') IS NOT NULL) DROP TABLE #XML
CREATE TABLE #XML (
Dados XML
)
INSERT INTO #XML
SELECT Tabela.coluna.query('.') AS Resultado
FROM @xml.nodes('/GeocodeResponse/result/address_component') Tabela(coluna)
IF (OBJECT_ID('tempdb..#Endereco') IS NOT NULL) DROP TABLE #Endereco
CREATE TABLE #Endereco (
Ds_Tipo VARCHAR(100),
Ds_Subtipo VARCHAR(100),
Ds_ShortName VARCHAR(200),
Ds_LongName VARCHAR(500)
)
INSERT INTO #Endereco
SELECT
Dados.query('address_component/type[1]').value('.', 'varchar(100)') AS Ds_Tipo,
Dados.query('address_component/type[2]').value('.', 'varchar(100)') AS Ds_Subtipo,
Dados.query('address_component/short_name').value('.', 'varchar(200)') AS Ds_ShortName,
Dados.query('address_component/long_name').value('.', 'varchar(500)') AS Ds_LongName
FROM
#XML
INSERT INTO #Endereco
SELECT
'formatted_address',
'formatted_address',
'',
@xml.value('(/GeocodeResponse/result/formatted_address)[1]', 'varchar(500)')
INSERT INTO #Endereco
SELECT
'latlon',
'latitude_longitude',
@xml.value('(/GeocodeResponse/result/geometry/location/lat)[1]', 'varchar(100)'),
@xml.value('(/GeocodeResponse/result/geometry/location/lng)[1]', 'varchar(100)')
SELECT
MAX(CASE WHEN Ds_Tipo = 'formatted_address' THEN Ds_LongName END) AS Ds_Endereco_Completo,
MAX(CASE WHEN Ds_Tipo = 'route' THEN Ds_LongName END) AS Ds_Logradouro,
MAX(CASE WHEN Ds_Tipo = 'street_number' THEN Ds_LongName END) AS Ds_Numero,
MAX(CASE WHEN Ds_Tipo = 'sublocality_level_1' THEN Ds_LongName END) AS Ds_Bairro,
MAX(CASE WHEN Ds_Tipo = 'administrative_area_level_2' THEN Ds_LongName END) AS Ds_Cidade,
MAX(CASE WHEN Ds_Tipo = 'postal_code' THEN Ds_LongName END) AS Ds_CEP,
MAX(CASE WHEN Ds_Tipo = 'administrative_area_level_1' THEN Ds_ShortName END) AS Ds_Estado_Sigla,
MAX(CASE WHEN Ds_Tipo = 'administrative_area_level_1' THEN Ds_LongName END) AS Ds_Estado,
MAX(CASE WHEN Ds_Tipo = 'country' THEN Ds_ShortName END) AS Ds_Pais_Sigla,
MAX(CASE WHEN Ds_Tipo = 'country' THEN Ds_LongName END) AS Ds_Pais,
MAX(CASE WHEN Ds_Tipo = 'latlon' THEN Ds_ShortName END) AS Ds_Latitude,
MAX(CASE WHEN Ds_Tipo = 'latlon' THEN Ds_LongName END) AS Ds_Longitude
FROM
#Endereco
END
If I EXECUTE this SP with one value like EXECUTE SP_BUSCAENDERECO @CEP ='04017090' it works just fine but I really need to work with multiple values.
What I really need to do is create a table or a view (or if you guys has a better approach) passing as parameter @CEP from a Select with Multiple Values (I have 1000 clients and I need to know they LATITUDE AND LONGITUDE COORDINATES from Google
The Client's select will be something like this
SELECT STUDENT.ID, PERSON.CEP FROM STUDENT LEFT JOIN PERSON ON STUDENT.CODPERSON = PERSON.ID)
Thank you so much
Regards
July 27, 2016 at 9:09 am
Have you considered a table-valued parameter?
John
July 27, 2016 at 9:26 am
You can pass multiple CEPs to your procedure using a table-valued parameter. If your lookup to Google doesn't accept multiple values, you will have to use a cursor or WHILE loop to work your ways through all the rows of the table valued parameter.
1) Create a User-Defined table type. This is rather like a class. It is a persistent definition of a table variable.
2) Modify your procedure to accept a table variable, using the table type from step 1
3) Modify your procedure to do the Google lookup for every row in the table.
4) Declare a table variable, using the table type from step 1.
5) Populate the table variable using the final query from your example.
6) Call your procedure passing the table variable instead of @CEP
A good example to get you started is found here.
https://msdn.microsoft.com/en-us/library/bb510489.aspx
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 28, 2016 at 5:21 am
Thanks guys. ill try to do it,
July 28, 2016 at 7:33 am
Slight side track here. I noticed you named your procedure with the SP_ prefix. This can cause some issues. You should consider using a different prefix, or even better no prefix at all. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply