Print ''
Print '----------------------------------------------------'
Print ' FirstRevision: V2.1.1   13.12.2012'
Print ' CurrRevision : V2.                '
Print ' Autor        : J.Feuerstein'
Print ' '
Print ' Database Name: #DB_N#'
Print '----------------------------------------------------'

 USE [#DB_N#]

 DECLARE @ExecuteSQLstr        nvarchar(max)
 DECLARE @SiteId               nvarchar(max)
 DECLARE @ProcStateCol         nvarchar(max)
 DECLARE @ProcStateValueRestTo nvarchar(max)
 DECLARE @TempTblName          nvarchar(32)

 -- #Doc: Insert your SiteId
 set @SiteId='#SiteId#'
 -- #Doc: Values: ProcStateCol01..10 ProcStateColRf
 set @ProcStateCol='ProcStateColRf'
 -- #Doc: Set Value you like to Use Default is 'N'
 set @ProcStateValueRestTo='N'

 print ''
 Print 'Process: Delete Processdata from Site:[' + @SiteId + ']  from Process Group:[' + @ProcStateCol + ']'
 Print '         Reset Status of Process Group:[' + @ProcStateCol + '] to:' + @ProcStateValueRestTo 

 set @TempTblName = '##zzzzztemp_tbl_DelProcessData'
 IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

 -- #Step: alle Objects_tbl.Id suchen der Site die im  @ProcStateCol den Wert D% oder P% haben
 --Print '#Step: Search :Objects_tbl Process Data'
 Print '#Step: Search: Objects_tbl.' +  @ProcStateCol + ' having Value to Value: D% or P%['
 set @ExecuteSQLstr = +
 'SELECT dbo.Objects_tbl.Id' +
 ' into ' + @TempTblName +
 ' FROM dbo.Objects_tbl' +
 ' where (' + @ProcStateCol + ' like ' + '''D%''' + ' or ' + @ProcStateCol + ' like ' + '''P%''' + ')' +
         ' and ObjectClass = ' + '''DS''' +
         ' and dbo.Objects_tbl.ObjectFromSiteID = ' + '''' + @SiteId + ''''

-- Print '#Debug: Sql-1: '
-- Print @ExecuteSQLstr
 execute sp_executesql @ExecuteSQLstr

 -- #Step: loeschen der Prozessdaten via @TempTblName
 Print '#Step: Delete.: JobControl_ObjectsProcessData_tbl Process Data'
 set @ExecuteSQLstr = +
 'DELETE' +
 ' FROM dbo.JobControl_ObjectsProcessData_tbl WHERE JobControl_ObjectsProcessData_tbl.Objects_tbl_ID IN' +
 ' (SELECT Id FROM ' + @TempTblName + ')'
-- Print '#Debug: Sql-2: '
-- Print @ExecuteSQLstr
 execute sp_executesql @ExecuteSQLstr

 -- #Step: Reset des Process Status
 Print '#Step: Reset.: ProcessStatus Objects_tbl.' +  @ProcStateCol + ' to Value:[' + @ProcStateValueRestTo + ']'
 set @ExecuteSQLstr = +
 'UPDATE Objects_tbl' +
         ' set ' + @ProcStateCol + ' = ' + '''' + @ProcStateValueRestTo + '''' +
         ' where Objects_tbl.ID IN' +
 ' (SELECT Id FROM ' + @TempTblName + ')'
-- Print '#Debug: Sql-3: '
-- Print @ExecuteSQLstr
 execute sp_executesql @ExecuteSQLstr

Print ''
Print '----------------------------------------------------'
Print ' SQL Script Excetuted on: [#DB_N#]'
Print '----------------------------------------------------'
GO
