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: Synchs the CPRS Status Types
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSyncStatusTypes]
@canCleanStagingTables bit
AS
DECLARE @batchLogID bigint
DECLARE @resultOk bit
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStatusTypes;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStatusTypes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStatusTypes;
END
--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableStatusTypes
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', 'zraw._conConsult');
--insert new records found from CPRSStatus in zraw._conConsult
INSERT INTO dbo.StatusTypes WITH (TABLOCK) (StatusTypeName)
SELECT DISTINCT CPRSStatus
FROM [zraw].[_conConsult] rawTx
INNER JOIN #batchTableStatusTypes bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE NOT EXISTS (SELECT 1 FROM dbo.StatusTypes ST
WHERE rawTx.CPRSStatus = ST.StatusTypeName)
AND rawTx.CPRSStatus IS NOT NULL;
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStatusTypes;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStatusTypes') IS NOT NULL
BEGIN
DROP TABLE #batchTableStatusTypes;
END
END TRY
BEGIN CATCH
PRINT 'There was an error synchronizing raw Status Type data' + CHAR(13);
THROW;
END CATCH
END