dbo.GS_RefreshEEGeocodeTrackerResults Stored Procedure

Properties

Creation Date	9/25/2015 5:17 PM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Data Type	Size
@EmployeeID	In	varchar	15
@DexID	In	varchar	15
@HomeCity	In	varchar	35
@HomeState	In	varchar	29
@WorkCity	In	varchar	35
@WorkState	In	varchar	29
@WorkLocationWithExplicitJuris	In	varchar	100
@RETURN_VALUE	Return Value	int	4

Objects that dbo.GS_RefreshEEGeocodeTrackerResults depends on

Database Object	Object Type	Dep Level
dbo.GS_EEGeocodeTrackerResultJurisdictions	Table	1
dbo.GS_EEGeocodeTrackerResults	Table	1
dbo.GS_EEGeocodeTrackerResultSuggestions	Table	1
dbo.GS_EEGeocodeTrackerResultUncertainPossibilities	Table	1

Procedure Source Code



--procedure for updating EEGeocodeTracker Results Log
--Returns Primary key for GS_EEGeocodeTrackerResults to reference on following insert(s) to GS_EEGeocodeTrackerResultSuggestions
create procedure dbo.GS_RefreshEEGeocodeTrackerResults 
(
@EmployeeID varchar(15), @DexID varchar(15), @HomeCity varchar(35), @HomeState varchar(29), @WorkCity varchar(35), @WorkState varchar(29), @WorkLocationWithExplicitJuris varchar(100)
) as 
BEGIN

IF EXISTS (select * from GS_EEGeocodeTrackerResults where EmployeeID = @EmployeeID) 
BEGIN
	DELETE from GS_EEGeocodeTrackerResultUncertainPossibilities where EmployeeID = @EmployeeID
	DELETE from GS_EEGeocodeTrackerResultSuggestions where EmployeeID = @EmployeeID
	DELETE from GS_EEGeocodeTrackerResultJurisdictions where EmployeeID = @EmployeeID
	DELETE from GS_EEGeocodeTrackerResults where EmployeeID = @EmployeeID
END

INSERT into GS_EEGeocodeTrackerResults (EmployeeID, DexID, HomeCity, HomeState, WorkCity, WorkState, WorkLocationWithExplicitJuris, Modified)
values (@EmployeeID, @DexID, @HomeCity, @HomeState, @WorkCity, @WorkState, @WorkLocationWithExplicitJuris, CURRENT_TIMESTAMP);
SELECT SCOPE_IDENTITY();
end

Related Objects

dbo Schema
SQL Objects Database