Notes

USE [Drama]
GO
/****** Object: StoredProcedure [dbo].[sp_GoogleMap] Script Date: 2018/1/24 上午 11:24:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author: David
-- Create date:
-- Description: Google定位
-- =============================================
ALTER PROCEDURE [dbo].[sp_GoogleMap]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @IDX int
DECLARE @CoordinateX2 float
DECLARE @CoordinateY2 float

DECLARE @tmpTable Table (IDX int
,CoordinateX2 float
,CoordinateY2 float);
INSERT INTO @tmpTable
SELECT s.IDX,CoordinateX2,CoordinateY2 from SCase as s WITH(NOLOCK)
join ETrust.Etrust.dbo.HCHouse as h WITH(NOLOCK) ON s.CaseNo=h.CaseNo and h.OwnerShopID=CASE When s.HeadCode='YC' THEN 'A001' When s.HeadCode='UT' THEN 'A002' ELSE 'A568' END+s.StoreCode
where GoogleMapLongi IS NULL and GoogleMapLati is NULL
AND webstate=1 and CoordinateX2 is not null

SELECT * from @tmpTable

WHILE EXISTS(SELECT TOP 1 IDX from @tmpTable)
BEGIN
SELECT TOP 1 @IDX=IDX,@CoordinateX2=CoordinateX2,@CoordinateY2=CoordinateY2 from @tmpTable
update SCase SET GoogleMapLongi=@CoordinateX2,GoogleMapLati=@CoordinateY2 where IDX=@IDX
DELETE FROM @tmpTable WHERE IDX = @IDX;
Select @IDX,@CoordinateX2,@CoordinateY2
End
END