USE [HolidayPublic] GO /****** Object: StoredProcedure [dbo].[usp_UpdateStores] Script Date: 2/1/2017 11:49:45 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================================================================================== -- Author: Corey Malczewski -- Create date: 04/14/2015 -- Description: Saves one order and marks it as done. -- ================================================================================================== CREATE PROCEDURE [dbo].[usp_UpdateStores] @StoresXML as xml as BEGIN DECLARE @TempStores TABLE ( Store int, Address1 [varchar](100), City [varchar](100), [State] [varchar](2) ,[Zipcode] [varchar](12), [Phone] [varchar](20), [Diesel] [varchar](4), [E85] [varchar](4), [NonOxygenated] [varchar](4), [Carwash] [varchar](4), UnlimitedCarWashPass [varchar](4), [Open24Hours] [varchar](4), [ATM] [varchar](20), [StoreType] [varchar](50), [Latitude] decimal(10, 7), [Longitude] decimal(10, 7), [Active] [varchar](50), BHR varchar (4), UnattendedFueling varchar (4), TruckStop varchar (4), DEF varchar (4), Propane varchar (4), CNG varchar (4), MoneyOrders varchar (4) ) -- , UnattendedFueling , TruckStop, DEF, Propane , CNG -- load into a temp table from xml insert into @TempStores ( Store, Address1, City , [State] ,[Zipcode], [Phone] , [Diesel], [E85], [NonOxygenated] , [Carwash], UnlimitedCarWashPass, [Open24Hours], [ATM], [BHR], [StoreType] , [Latitude], [Longitude], [Active] , UnattendedFueling , TruckStop, DEF, Propane , CNG, MoneyOrders ) SELECT ParamValues.ID.value('Store[1]','int') as Store, ParamValues.ID.value('address1[1]','varchar(100)') as Address1, ParamValues.ID.value('city[1]','varchar(100)') as City, ParamValues.ID.value('state[1]','varchar(2)') as [State], ParamValues.ID.value('zipCode[1]','varchar(12)') as Zipcode, ParamValues.ID.value('phone[1]','varchar(20)') as Phone, ParamValues.ID.value('Diesel[1]','varchar(4)') as Diesel, ParamValues.ID.value('E85[1]','varchar(4)') as [E85], ParamValues.ID.value('NonOxygenated[1]','varchar(4)') as NonOxygenated, ParamValues.ID.value('CarWash[1]','varchar(4)') as [Carwash], ParamValues.ID.value('CarWashUnlimitedPass[1]','varchar(4)') as UnlimitedCarWashPass, ParamValues.ID.value('Open24Hours[1]','varchar(4)') as Open24Hours, ParamValues.ID.value('ATM[1]','varchar(20)') as ATM, ParamValues.ID.value('BHR[1]','varchar(4)') as BHR, ParamValues.ID.value('StoreType[1]','varchar(50)') as StoreType, ParamValues.ID.value('latitude[1]','decimal(10, 7)') as Latitude, ParamValues.ID.value('longitude[1]','decimal(10, 7)') as Longitude, ParamValues.ID.value('Active[1]','varchar(4)') as Active, ParamValues.ID.value('UnattendedFueling[1]','varchar(4)') as UnattendedFueling, ParamValues.ID.value('TruckStop[1]','varchar(4)') as TruckStop, ParamValues.ID.value('DEF[1]','varchar(4)') as DEF, ParamValues.ID.value('BulkPropane[1]','varchar(4)') as Propane, ParamValues.ID.value('CNG[1]','varchar(4)') as CNG, ParamValues.ID.value('MoneyOrders[1]','varchar(4)') as MoneyOrders FROM @StoresXML.nodes('/Records/Record') as ParamValues(ID) -- Blue Planet is being used for Ethonal Preium/ non Oxy insert into StoreLocations(Store, Address1, City , [State] ,[Zipcode], [Phone] , [Diesel], [E85], BluePlanet, [Carwash], UnlimitedCarWashPass, [Open24Hours], [ATM], [BHR], [StoreType] , [Latitude], [Longitude], [Active], UnattendedFueling, TruckStop , DEF , Propane, CNG, MoneyOrders ) SELECT Store, Address1, City , [State] ,[Zipcode], [Phone] , [Diesel], [E85], [NonOxygenated] , [Carwash], UnlimitedCarWashPass, [Open24Hours], [ATM], [BHR], [StoreType] , [Latitude], [Longitude], [Active], UnattendedFueling, TruckStop , DEF , Propane, CNG, MoneyOrders FROM @TempStores as Temp where Temp.Store not in (SELECT Store from StoreLocations) -- incase a store was removed in asset Update StoreLocations SET Active = 'No' where Store not in (SELECT Store from @TempStores) -- Update the games if the DB is stale Update StoreLocations SET Store = Temp.Store ,Address1 = Temp.Address1 ,City = Temp.City ,[State] = Temp.[State] ,Zipcode = Temp.Zipcode ,Phone = Temp.Phone ,Diesel = Temp.Diesel ,[E85] = Temp.[E85] ,BluePlanet = Temp.NonOxygenated ,[Carwash] = Temp.[Carwash] ,UnlimitedCarWashPass = Temp.UnlimitedCarWashPass ,Open24Hours = Temp.Open24Hours ,ATM = Temp.ATM ,BHR = Temp.BHR ,StoreType = Temp.StoreType ,Latitude = Temp.Latitude ,Longitude = Temp.Longitude ,Active = Temp.Active ,UnattendedFueling = Temp.UnattendedFueling ,TruckStop = Temp.TruckStop ,DEF = Temp.DEF ,Propane = Temp.Propane ,CNG = Temp.CNG ,MoneyOrders = Temp.MoneyOrders from StoreLocations inner JOIN @TempStores as Temp ON StoreLocations.Store = Temp.Store -- Update StoreLocations Set HoursMonday ='24 Hours', HoursTuesday ='24 Hours', HoursWednesday ='24 Hours', HoursThursday ='24 Hours', HoursFriday ='24 Hours', HoursSaturday ='24 Hours', HoursSunday ='24 Hours' -- where Open24Hours='Yes' END GO