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