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