Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?-- =============================================
-- Description: Cleans the Stop Codes
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoCleanStopCodes]
AS
DECLARE @TABLE_NAME varchar(100); --make constant value for use thoughout
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
--Set @TABLE_NAME value
SET @TABLE_NAME = 'zraw._dimStopCode';

--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStopCodes;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStopCodes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStopCodes;
END

--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableStopCodes
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', @TABLE_NAME);

--LTRIM/RTRIM any string-based fields
UPDATE zraw._dimStopCode SET
StopCodeName = NULLIF(LTRIM(RTRIM(StopCodeName)), '')
, OpCode = NULLIF(LTRIM(RTRIM(OpCode)), '')
WHERE EXISTS (SELECT 1 FROM #batchTableStopCodes
WHERE zraw._dimStopCode.BatchLogID = #batchTableStopCodes.BatchLogID);

--set RecordStatusID based on OpCode
UPDATE zraw._dimStopCode SET
RecordStatusID = RecordStatuses.RecordStatusID
FROM RecordStatuses
INNER JOIN zraw._dimStopCode ON RecordStatuses.RecordStatusCode = zraw._dimStopCode.OpCode
WHERE EXISTS (SELECT 1 FROM #batchTableStopCodes
WHERE zraw._dimStopCode.BatchLogID = #batchTableStopCodes.BatchLogID);


--create any needed indexes to speed up cleaning

--set StationIDs (only have ParentStations to worry about (VAMCs))
UPDATE zraw._dimStopCode SET
StationID = Stations.StationID
FROM Stations
INNER JOIN zraw._dimStopCode ON Stations.Station3N = zraw._dimStopCode.Sta3n
WHERE Stations.ParentStation = 1
AND EXISTS (SELECT 1 FROM #batchTableStopCodes
WHERE zraw._dimStopCode.BatchLogID = #batchTableStopCodes.BatchLogID);

--drop any raw index objects

--drop the ##batchTableStopCodes if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStopCodes;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStopCodes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStopCodes;
END

END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableStopCodes
WHERE BatchLogs.BatchLogID = #batchTableStopCodes.BatchLogID);

--drop any raw index objects

--drop the ##batchTableStopCodes if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStopCodes;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStopCodes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStopCodes;
END

PRINT 'There was an error cleaning raw Stop Code data' + CHAR(13);
THROW;
END CATCH
END