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: Delete supplied batch from raw tables.
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoDeleteRaw]
@batchLogId bigint,
@rawTableName varchar(200),
@resultOK int OUTPUT --OUTPUT is a convenience for Batch
AS
BEGIN
DECLARE @recCount bigint
DECLARE @nDynamicSQL nvarchar(max);
DECLARE @nParameter_Definition nvarchar(max);
--Since SAS can't do the alternative xVal = EXEC usp... approach, the OUTPUT variable is used
SET @resultOK = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--do the while loop for each raw table
SET @nDynamicSQL = N'SELECT @recCount = COUNT(1)' +
' FROM ' + @rawTableName +
' WHERE BatchLogID = ' + CAST(@batchLogId AS varchar);
--set the dynamic sql definition parameters
SET @nParameter_Definition = N'
@recCount bigint OUTPUT';
--Get the maximum date synchronized
EXEC sp_executesql @nDynamicSQL
, @nParameter_Definition
, @recCount = @recCount OUTPUT;
WHILE @recCount <> 0
BEGIN
SET @nDynamicSQL = N'DELETE TOP(100000) ' +
' FROM ' + @rawTableName +
' WHERE BatchLogID = ' + CAST(@batchLogId AS varchar);
EXEC sp_executesql @nDynamicSQL;
--shrink log file
--Have to comment these professionally designed commands out due to
--the business agreeing to all of CDW's snake oil. Must taste good.
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS;
--shrink staging data
--DBCC SHRINKFILE (N'StagingData1' , 0, TRUNCATEONLY) WITH NO_INFOMSGS;
--Have to reset the counter to zero prior to dynamic SQL execution,
--or else will get stuck in endless loop if return value is zero.
SET @recCount = 0;
--check to see if there are any more records to delete for the batch
--if so, loop back through again
SET @nDynamicSQL = N'SELECT @recCount = COUNT(1)' +
' FROM ' + @rawTableName +
' WHERE BatchLogID = ' + CAST(@batchLogId AS varchar);
--set the dynamic sql definition parameters
SET @nParameter_Definition = N'
@recCount bigint OUTPUT';
END
SET @resultOK = 1;
--To help out with SAS, also returning SELECT on the OUTPUT param
SELECT @resultOK AS ResultOK;
END TRY
BEGIN CATCH
PRINT 'There was an error deleting zraw records' + CHAR(13);
THROW;
END CATCH
END