Partial Request Rejection Script
Run this script to create the stored procedure webRequest_Submit_ParentRequestRejectedRecordsIns.
CREATE PROCEDURE [dbo].[webRequest_Submit_ParentRequestRejectedRecordsIns] @RequestID INT, @ParentRequestID INT, @ScenarioID UNIQUEIDENTIFIER AS BEGIN SET NOCOUNT ON; DECLARE @ParentScenario UNIQUEIDENTIFIER, @GetRecordsRequestID INT, @PageCount INT, @LC INT, @BaseTableName NVARCHAR(50), @SQL NVARCHAR(max), @DataBase NVARCHAR(50), @WebAppID UNIQUEIDENTIFIER, @strSql NVARCHAR(max), @strSql2 NVARCHAR(max), @strFields NVARCHAR(max) DECLARE @PageTable Table ( ID INT IDENTITY(1,1), TableName NVARCHAR(50)) SELECT @ParentScenario = ScenarioID FROM ttRequest WHERE RequestID=@ParentRequestID SELECT @WebAppID=WebAppID FROM DGE..ttRequest WHERE RequestID=@RequestID SELECT @DataBase= [Database] FROM DGE..webCategory_DatabaseSel WHERE WebAppID=@WebAppID --Only begin insertion If current request is recursive request IF (@RequestID<>@ParentRequestID AND @ScenarioID=@ParentScenario) BEGIN SELECT @GetRecordsRequestID = MAX(RequestID) FROM DGE..ttRequest WHERE ParentRequestID=@ParentRequestID AND @ScenarioID=@ParentScenario AND RequestID<>@RequestID --select All table names used in current request INSERT INTO @PageTable SELECT DISTINCT [Table] FROM CranSoft.dbo.[Page] WHERE PageID IN ( SELECT DISTINCT PageID FROM DGE..ttTaskPage WHERE TaskID IN ( SELECT DISTINCT SRT.TaskID FROM DGE..ttScenarioRoleTask SRT INNER JOIN DGE..ttTask T ON SRT.TaskID=T.TaskID WHERE ScenarioID=@ScenarioID AND ActiveInScenarioRole=1 AND T.[Type]='Application' )) SELECT @PageCount = Count([TableName]) FROM @PageTable SET @LC=1 While @LC <=@PageCount BEGIN SELECT @BaseTableName = TableName FROM @PageTable WHERE ID = @LC --If rejected column does not esixt in table then skip IF NOT EXISTS(SELECT 1 FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name = @BaseTableName and syscolumns.name = 'Rejected' ) BEGIN SET @LC+=1 CONTINUE END --To prevent duplication of records in request SET @SQL = 'DELETE FROM '+ @BaseTableName + ' Where RequestID = '+ CONVERT(NVARCHAR, @RequestID) EXECUTE DGE..webExecuteSQL @SQL, @DataBase SET @SQL=NULL --Create temp Table SET @SQL = 'Select * INTO ##TempTable'+CONVERT(NVARCHAR,@LC)+' FROM '+ @BaseTableName + ' Where RequestID = ' + CONVERT(NVARCHAR, @GetRecordsRequestID) + ' AND Rejected = 1' EXECUTE DGE..webExecuteSQL @SQL, @DataBase SET @SQL=NULL --Update RequestID in temp table SET @SQL ='UPDATE ##TempTable'+CONVERT(NVARCHAR,@LC)+' SET RequestID = ' + CONVERT(NVARCHAR,@RequestID) EXECUTE DGE..webExecuteSQL @SQL, @DataBase SET @SQL=NULL ----Insert records into base table ------------------------------------- DECLARE curFieldHolder CURSOR FOR -- Select all fields from the intersection of database objects SELECT dbo.syscolumns.name FROM dbo.sysobjects INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id WHERE ( dbo.syscolumns.name IN (SELECT dbo.syscolumns.name FROM dbo.sysobjects INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id WHERE ( dbo.sysobjects.name = @BaseTableName ) AND dbo.syscolumns.name NOT IN (SELECT [Column] FROM CranSoft.dbo.ReservedColumn WHERE [Column] NOT IN ('AddedOn', 'AddedBy') UNION ALL SELECT 'ID' UNION ALL SELECT 'Rejected' UNION ALL SELECT 'RejectionReason'))) AND ( dbo.sysobjects.name = @BaseTableName ) DECLARE @ColumnName1 sysname -- Loop through the cursor and update all fields in the first object with corresponding fields in the second object OPEN curFieldHolder FETCH curFieldHolder INTO @ColumnName1 SELECT @strFields = '' WHILE ( @@FETCH_STATUS = 0 ) BEGIN -- Execute the SQL updates SELECT @strFields = @strFields + '[' + @ColumnName1 + ']' FETCH curFieldHolder INTO @ColumnName1 IF ( @@FETCH_STATUS = 0 ) BEGIN SELECT @strFields = @strFields + ', ' END END CLOSE curFieldHolder DEALLOCATE curFieldHolder SET @strSql = 'INSERT INTO dbo.' + @BaseTableName + ' ('+ @strFields + ') ' SET @strSql2 = 'SELECT ' + @strFields + + ' FROM ##TempTable'+CONVERT(NVARCHAR,@LC) + ' WHERE (RequestID = ' + CONVERT(NVARCHAR(20), @RequestID) + ')' EXEC(@strSql + @strSql2) ------------------------------------- --Drop temp table SET @SQL= 'DROP TABLE IF EXISTS ##TempTable'+CONVERT(NVARCHAR,@LC) EXECUTE DGE..webExecuteSQL @SQL, @DataBase SET @LC+=1 END END END GO