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: Slices the desired Station6As from the table and field into a Station6A
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoDynamicSliceStation6A]
(
@tableName varchar(200),
@fieldName varchar(200),
@ignoreBatching bit
)
AS
BEGIN
/************
NOTE! This script requires two fields to be filled--StationID and Station6A
Failure to have these two fields will result in a big kaboom.
************/
DECLARE @nDynamicSQL nvarchar(max),
@nParameter_Definition nvarchar(max);
DECLARE @nextStationId smallint,
@nextStation6A varchar(10);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--If the proc hasn't received the appropriate info, then skip any processing
--Both @tableName and @fieldName are required!
IF NULLIF(LTRIM(RTRIM(@tableName)), '') IS NULL OR NULLIF(LTRIM(RTRIM(@fieldName)), '') IS NULL
RETURN(1);
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableDynamic;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableDynamic') IS NOT NULL
BEGIN
DROP TABLE #batchTableDynamic;
END
--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableDynamic
FROM dbo.ufnGetBatchLogBatchDate ('INTPROCESSING', @tableName);
--get table of station6a's
--SQL 2016 script-- DROP TABLE IF EXISTS #station6As;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#station6As') IS NOT NULL
BEGIN
DROP TABLE #station6As;
END
SELECT DISTINCT StationID, Station6A
INTO #station6As
FROM Stations
WHERE ParentStation = 0
AND TryParseStation6A = 1;
--Set initial stationId
SELECT @nextStationId = MIN(StationID)
FROM #station6As;
WHILE @nextStationId <> 0
BEGIN
SELECT @nextStation6A = Station6A
FROM #station6As
WHERE StationID = @nextStationId;
--Do the slicing updates via dynamic sql script.
SET @nDynamicSQL = N'UPDATE ' + @tableName + ' SET ' +
' Station6A = ' + @nextStation6A +
', StationID = ' + @nextStationId +
' WHERE ' + @fieldName + ' LIKE ''%' + @nextStation6A + '%''' +
' AND Station6A IS NULL' +
' AND BatchLogID EXISTS (SELECT 1 FROM #batchTableDynamic ' +
' WHERE #batchTableDynamic.BatchLogID = ' + @tableName + '.BatchLogID);';
--set the dynamic sql definition parameters
SET @nParameter_Definition = N'
@tableName varchar(200)
, @fieldName varchar(200)
, @nextStation6A varchar(10)';
--Get the maximum date synchronized
EXEC sp_executesql @nDynamicSQL
, @nParameter_Definition
, @tableName = @tableName
, @fieldName = @fieldName
, @nextStation6A = @nextStation6A;;
--iterate to the next stationId in the list.
SELECT @nextStationId = MIN(StationID)
FROM #station6As
WHERE StationID > @nextStationId;
END
--Final clean-up
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableDynamic;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableDynamic') IS NOT NULL
BEGIN
DROP TABLE #batchTableDynamic;
END
--SQL 2016 script-- DROP TABLE IF EXISTS #station6As;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#station6As') IS NOT NULL
BEGIN
DROP TABLE #station6As;
END
END TRY
BEGIN CATCH
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableDynamic;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableDynamic') IS NOT NULL
BEGIN
DROP TABLE #batchTableDynamic;
END
--SQL 2016 script-- DROP TABLE IF EXISTS #station6As;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#station6As') IS NOT NULL
BEGIN
DROP TABLE #station6As;
END
PRINT 'There was an error slicing ' + @tableName + ' Station6A data' + CHAR(13);
THROW;
END CATCH
END