Solved: Need Help...SQL Stored Procedure runs in DA SQL S...

chinmay · ‎06-10-2014

All,

The below SP run in our DA SQL server. Could you please help us to understand what does this script will do? does this is configured any whehre in application to execute this stored procedure?

USE [EVDAADCHolds2009andEarlier]

GO

/****** Object: StoredProcedure [dbo].[usp_Case_DoDel] Script Date: 06/10/2014 10:25:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_Case_DoDel]

@QuitAfter int = 240, --SECONDS

@ChunkSize int = 1000,

@DelayFor varchar(12) = '00:00:00.010',

@FolderRetentionPeriod int = 0, -- Number of DAYS deleted folders will be stored: -1 For Ever

@CaseRetentionPeriod int = -1, -- Number of DAYS deleted Cases will be stored: -1 for ever

@deadlock_var NCHAR(10) = 'LOW' -- USE Only "LOW" or "NORMAL" (SQL 2000 options)

AS

-- by default, this SP should be choosen as a deadlock victim in case of problem

-- To change this you will need to specify @deadlock_var='NORMAL' (for SQL 2000)

if(@deadlock_var is null) SET @deadlock_var = N'LOW'

SET DEADLOCK_PRIORITY @deadlock_var;

SET NOCOUNT ON

DECLARE @bContinue BIT

SET @bContinue = 1

-- delete all the searches

DECLARE @NumOfRows int

DECLARE @RowCounter int

SET @RowCounter = 1

DECLARE @StartTime datetime

DECLARE @ReturnCode int

DECLARE @CaseID int

DECLARE @FolderType int

DECLARE @ModifiedDate DateTime

DECLARE @MSG varchar(300)

if(@QuitAfter < 0 ) SELECT @QuitAfter = NULL

-- before starting the delete of case do a bit of house clean.

-- delete all the folders which should be deleted because the retention period has passed

if(@FolderRetentionPeriod>-1)

begin

DELETE FROM tblCase WHERE StatusID = 24 AND FolderType <> 330 AND DATEDIFF(day, ModifiedDate, GetUtcDate() )>= @FolderRetentionPeriod

SELECT @MSG = ltrim(rtrim(str(@@ROWCOUNT))) + ' Folders set to status 24 (deleted) are now deleted '

PRINT @MSG

end

-- delete all the cases which should be deleted because the retention period has passed

if(@CaseRetentionPeriod>-1)

begin

DELETE FROM tblCase WHERE StatusID = 24 AND FolderType = 330 AND DATEDIFF(day, ModifiedDate, GetUtcDate() )>= @CaseRetentionPeriod

SELECT @MSG = ltrim(rtrim(str(@@ROWCOUNT))) + ' Cases set to status 24 (deleted) are now deleted '

PRINT @MSG

end

SELECT @StartTime = GetUtcDate()

-- Get the list of SearchID's that need to be deleted

DECLARE @SearchIDToDeleteSearchVaultsFromCounter int

DECLARE @NumSearchIDToDeleteSearchVaults int

DECLARE @tblSearchIDToDeleteSearchVaults TABLE(RowNum int IDENTITY, SearchID int)

DECLARE @PrimaryKeyValue INT

SET @SearchIDToDeleteSearchVaultsFromCounter = 1

INSERT INTO @tblSearchIDToDeleteSearchVaults( SearchID )

-- get a list of searchids that have been accepted but still have items in tblsearchitems and have been archived!

SELECT distinct s.SearchID from tblintSearches s inner join tblSearchVault v ON s.SearchID = v.SearchID

AND s.StatusID = 7 and vaultSearchesArchiveStatus = 1

SELECT @NumSearchIDToDeleteSearchVaults = count(*) from @tblSearchIDToDeleteSearchVaults

if (@NumSearchIDToDeleteSearchVaults > 0)

BEGIN

WHILE (@SearchIDToDeleteSearchVaultsFromCounter <= @NumSearchIDToDeleteSearchVaults)

BEGIN

SELECT @PrimaryKeyValue = SearchID FROM @tblSearchIDToDeleteSearchVaults WHERE RowNum = @SearchIDToDeleteSearchVaultsFromCounter

-- delete all the items from the tblSearchItems table forthe given searchid

if( exists(select SearchVaultID from tblSearchVault with (nolock) where SearchID = @PrimaryKeyValue ) )

begin

if not exists(select 1 from tblsearchresults with (nolock) where searchid = @PrimaryKeyValue)

begin

exec @ReturnCode = usp_DoGeneric_Del @PrimaryKeyValue = @PrimaryKeyValue, @ChunkSize = @ChunkSize, @TableName = 'tblSearchVault', @PrimaryKeyColumn='SearchID', @QuitAfter = 60, @DelayFor = @DelayFor, @StartTime = @StartTime, @deadlock_var=@deadlock_var

--IF(@ReturnCode = -1) RETURN -1

end

SET @SearchIDToDeleteSearchVaultsFromCounter = @SearchIDToDeleteSearchVaultsFromCounter + 1

END

SELECT @StartTime = GetUtcDate()

-- Get the list of cases which need to be deleted

DECLARE @CaseToDeleteRowCounter int

DECLARE @CaseToDeleteNumOfRows int

DECLARE @CaseToDelete TABLE(RowNum int IDENTITY, CaseID int, FolderType int, ModifiedDate DateTime )

INSERT INTO @CaseToDelete(CaseID, FolderType, ModifiedDate) SELECT CaseID, FolderType, ModifiedDate FROM tblCase WHERE MarkedForDeletion = 1 and StatusID<> 24 ORDER BY NewID()

SET @CaseToDeleteRowCounter = 1

SELECT @CaseToDeleteNumOfRows = count(*) FROM @CaseToDelete

IF(@CaseToDeleteNumOfRows > 0)

BEGIN

WHILE @CaseToDeleteRowCounter <= @CaseToDeleteNumOfRows

BEGIN

SELECT @CaseID = CaseID, @FolderType=FolderType, @ModifiedDate=ModifiedDate FROM @CaseToDelete WHERE RowNum = @CaseToDeleteRowCounter

BEGIN

if(@FolderType = 330)

SELECT @MSG = 'processing Case ID ' + ltrim(rtrim(str(@CaseID)))

if(@FolderType = 331)

SELECT @MSG = 'processing Folder ID ' + ltrim(rtrim(str(@CaseID)))

if(@FolderType = 332)

SELECT @MSG = 'processing Hidden Folder ID ' + ltrim(rtrim(str(@CaseID)))

PRINT @MSG

-- update all the searchVault to Error to stop the threads picking up the searches

update tblSearchVault set StatusID = 14 where StatusID not in (14, 19) and searchid in (select searchid from tblIntSearches where CaseID=@CaseID)

SELECT @MSG = ltrim(rtrim(str(@@ROWCOUNT))) + ' search vaults marked for deletion for Case ID ' + ltrim(rtrim(str(@CaseID)))

PRINT @MSG

-- We are going to use the delete search threads to do the job to delete the searches of this case

-- just mark them to 4 rejected. Only do this for searches that are in a 'stable' state - if the search

-- is still doing stuff, e.g. in searching state or accepting state, let it finish first, otherwise we'll

-- potentially encounter race conditions while deleting.

update tblIntSearches set StatusID = 4 where CaseID=@CaseID and StatusID in (2,3,7,9,10,173,174,175)

SELECT @MSG = ltrim(rtrim(str(@@ROWCOUNT))) + ' searches marked for deletion for Case ID ' + ltrim(rtrim(str(@CaseID)))

PRINT @MSG

if not exists(select searchid from tblIntSearches where CaseID=@CaseID AND StatusID <> 858)

begin

SELECT @MSG = 'Case ID ' + ltrim(rtrim(str(@CaseID))) + ' all searches deleted start deleting other data '

PRINT @MSG

-- Only when all the searches have been deleted, start to delete the rest

-- delete all the production runs

DECLARE @ProductionID int

SET @RowCounter = 1

-- get a list of all production runs linked to this folder

DECLARE @ProductionIDList TABLE(RowNum int IDENTITY, ProductionID int )

INSERT INTO @ProductionIDList Select ProductionID from tblProduction where CaseID = @CaseID

SELECT @NumOfRows = count(*) FROM @ProductionIDList

IF(@NumOfRows > 0)

BEGIN

SELECT @MSG = 'Case ID ' + ltrim(rtrim(str(@CaseID))) + ' deleting production starting'

PRINT @MSG

WHILE @RowCounter <= @NumOfRows

BEGIN

SELECT @ProductionID = ProductionID FROM @ProductionIDList WHERE RowNum = @RowCounter

-- delete production items

exec usp_Production_del @ProductionID = @ProductionID, @QuitAfter = @QuitAfter, @ChunkSize = @ChunkSize, @DelayFor = @DelayFor

IF(@QuitAfter is not null AND DATEDIFF(second, @StartTime, GetUtcDate() )> @QuitAfter)

RETURN -1

SET @RowCounter = @RowCounter + 1

END

SELECT @MSG = 'Case ID ' + ltrim(rtrim(str(@CaseID))) + ' deleting production done'

PRINT @MSG

END

-- delete everything from other tables

exec @ReturnCode = usp_DoGeneric_Del @PrimaryKeyValue = @CaseID, @ChunkSize = @ChunkSize, @TableName = 'tblIntSecurity', @PrimaryKeyColumn='CaseID', @StartTime = @StartTime, @DelayFor = @DelayFor, @QuitAfter = @QuitAfter