|
Create procedure CleanupUncompleteActivity @ActivityName nvarchar (128),
@ActivityId
nvarchar(128)=NULL,
@DateThreshold
datetime=NULL,
@NewTableExtension
nvarchar(30)=NULL
as
--stored procedure body
DECLARE @BAMDBPrefix nvarchar(500 )
DECLARE @QueryString nvarchar(4000 )
DECLARE @ActiveTableName sysname
DECLARE @ActiveRelationshipsTableName sysname
DECLARE @ContinuationsTableName sysname
DECLARE @DanglingActiveTableName sysname
DECLARE @DanglingActiveRelationshipsTableName sysname
DECLARE @DanglingContinuationsTableName sysname
SET @BAMDBPrefix= 'BAMPrimaryImport.dbo.'
SET @ActiveTableName = 'bam_' + @ActivityName + '_Active'
SET @ActiveRelationshipsTableName = 'bam_' + @ActivityName + '_ActiveRelationships'
SET @ContinuationsTableName = 'bam_' + @ActivityName + '_Continuations'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
DECLARE @LockActivity nvarchar(128 )
SELECT @LockActivity = ActivityName
FROM BAMPrimaryImport.dbo.bam_Metadata_Activities WITH (XLOCK)
WHERE ActivityName = @ActivityName
EXEC sp_tables @table_name = #DanglingActivities
IF @@ROWCOUNT > 0 DROP TABLE #DanglingActivities
CREATE TABLE #DanglingActivities( ActivityID nvarchar(128) PRIMARY KEY)
SET @QueryString = N'INSERT INTO #DanglingActivities (ActivityID) SELECT ActivityID FROM ' + @BAMDBPrefix + 'bam_' + @ActivityName + '_Active'
IF (@DateThreshold is not NULL) OR (@ActivityId is not NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE'
END
IF (@DateThreshold is not NULL)
BEGIN
SET @QueryString = @QueryString + ' LastModified < N''' + CONVERT (nvarchar(50), @DateThreshold , 109) + ''''
IF (@ActivityId is not NULL)
BEGIN
SET @QueryString = @QueryString + ' AND'
END
END
IF (@ActivityId is not NULL)
BEGIN
SET @QueryString = @QueryString + ' ActivityID = N''' + @ActivityId + ''''
END
EXEC sp_executesql @QueryString
SELECT * FROM #DanglingActivities
SET @QueryString = N''
-- If the user gave a table extension, the dangling instances will be inserted
-- into that table.
IF (isnull(@NewTableExtension , '') <> '')
BEGIN
SET @DanglingActiveTableName = @ActiveTableName + '_' + @NewTableExtension
SET @DanglingActiveRelationshipsTableName = @ActiveRelationshipsTableName + '_' + @NewTableExtension
SET @DanglingContinuationsTableName = @ContinuationsTableName + '_' + @NewTableExtension
-- If the table for the dangling instances exist then insert into it
-- If the table does not exist, then create the dangling instances table
-- and then insert into it. SELECT INTO will do that.
-- Use [BAMArchive]
EXEC sp_tables @table_name = @DanglingActiveTableName
--USE [BAMPrimaryImport]
IF @@ROWCOUNT > 0
BEGIN
SET @QueryString = N'INSERT INTO ' + '[' + @DanglingActiveTableName + '] SELECT active.* FROM ' + @BAMDBPrefix + @ActiveTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
EXEC sp_executesql @QueryString
END
ELSE
BEGIN
SET @QueryString = N'SELECT active.* INTO [' + @DanglingActiveTableName + '] FROM ' + @BAMDBPrefix + @ActiveTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
select @QueryString
EXEC sp_executesql @QueryString
END
-- Now do what you did for the Active Instances table for the
-- ActiveRelationships table
EXEC sp_tables @table_name = @DanglingActiveRelationshipsTableName
IF @@ROWCOUNT > 0
BEGIN
SET @QueryString = N'INSERT INTO ' + '[' + @DanglingActiveRelationshipsTableName + '] SELECT active.* FROM ' + @BAMDBPrefix + @ActiveRelationshipsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
EXEC sp_executesql @QueryString
END
ELSE
BEGIN
SET @QueryString = N'SELECT active.* INTO [' + @DanglingActiveRelationshipsTableName + '] FROM ' + @BAMDBPrefix+ @ActiveRelationshipsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
EXEC sp_executesql @QueryString
END
-- And finally for the continuations table
EXEC sp_tables @table_name = @DanglingContinuationsTableName
IF @@ROWCOUNT > 0
BEGIN
SET @QueryString = N'INSERT INTO ' + '[' + @DanglingContinuationsTableName + '] SELECT active.* FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID'
EXEC sp_executesql @QueryString
END
ELSE
BEGIN
SET @QueryString = N'SELECT active.* INTO [' + @DanglingContinuationsTableName + '] FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID'
EXEC sp_executesql @QueryString
END
END
-- Remove the dangling instances from the Active Instances Table
SET @QueryString = 'DELETE FROM ' + @BAMDBPrefix + @ActiveTableName + ' FROM ' + @BAMDBPrefix + @ActiveTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID '
EXEC sp_executesql @QueryString
SET @QueryString = 'DELETE FROM ' + @BAMDBPrefix + @ActiveRelationshipsTableName + ' FROM ' + @BAMDBPrefix + @ActiveRelationshipsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID '
EXEC sp_executesql @QueryString
SET @QueryString = 'DELETE FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID '
EXEC sp_executesql @QueryString
DROP TABLE #DanglingActivities
COMMIT TRAN
GO |