Data is Null. The method or property cannot be call on null values. Exception

AmarRudra · ‎Apr 29 2024

In my application multiple users request the API and in the API I am calling the stored Procedure via Entity Framework but some times getting exception Data Is Null. The Method or Property cannot be call on null values. This is not consistent if 4-5 users are clicking on submit button to call api then api executing stored procedure then for some users exception coming.

This is my main procedure

ALTER PROCEDURE [dbo].[sp_SaveDcumentAndParties](
@Action VARCHAR(20),
@partiesHistoryJson NVARCHAR(MAX)=NULL,
@exportExtraHistoryJson NVARCHAR(MAX)=NULL,
@FieldSettingsJson NVARCHAR(MAX)=NULL,
@CodingSessionDetails NVARCHAR(MAX)=NULL,
@DocumentID VARCHAR(50) = NULL,
@ProjectId VARCHAR(50) = NULL,
@DocumentDate NVARCHAR(255) = NULL,
@DocumentType NVARCHAR(255) = NULL,
@EnteredById INT = NULL,
@Estimated NVARCHAR(255) = NULL,
@Title NVARCHAR(4000) = NULL,
@CodingQATime INT = NULL,
@IsCorrected INT = NULL,
@UserTask VARCHAR(10) = NULL,
@isHistoryDocument Bit,
@ReturnJSONResult NVARCHAR(MAX) =NULL
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ErrorMessage NVARCHAR(1000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

Declare @Description NVARCHAR(MAX);

IF @Action = 'Save'
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    BEGIN TRY

    -- Deadlock avoidance mechanism
    SET DEADLOCK_PRIORITY LOW;

    DECLARE @IsAddPartiesAndPartiesHistory BIT;
    DECLARE @IsAddCodedData BIT;
    DECLARE @IsUpdateImportPages BIT;
    DECLARE @IsUpdateAdminRegeTitle BIT;
    DECLARE @IsUpdateExportExtras BIT;
    DECLARE @IsInsUpdFieldAdminValidations BIT;
    DECLARE @IsExecCodingSessionDetails BIT;

    SAVE TRANSACTION MySavepoint;  -- Savepoint before inner procedure call

    EXEC AddPartiesAndPartiesHistory @partiesHistoryJson,@exportExtraHistoryJson, 
    @DocumentID,@IsReturn=@IsAddPartiesAndPartiesHistory OUTPUT;

    EXEC AddCodedData @DocumentID,@ProjectId, @DocumentDate,@DocumentType,@EnteredById,
        @Estimated,@Title,@CodingQATime,@IsCorrected,@UserTask,@IsReturn=@IsAddCodedData 
    OUTPUT;

    EXEC UpdateImportPages @DocumentID,@DocumentType, 
    @EnteredById,@UserTask,@IsReturn=@IsUpdateImportPages OUTPUT;

    EXEC UpdateAdminRegeTitle @DocumentID,@DocumentType, 
    @EnteredById,@Title,@DocumentDate,@Estimated,@UserTask,@IsReturn=@IsUpdateAdminRegeTitle 
    OUTPUT;

    If @UserTask='Coder'
        Begin
            EXEC UpdateExportExtras @DocumentID,@IsReturn=@IsUpdateExportExtras OUTPUT;

            EXEC InsUpdFieldAdminValidations 
        @DocumentID,@FieldSettingsJson,@IsReturn=@IsInsUpdFieldAdminValidations OUTPUT;

        END;


        DECLARE @DocOrgID INT = (SELECT Id + 1 FROM ImportPages Where 
        Document_ID=@DocumentID);
        DECLARE @NextDocumentId NVARCHAR(100);
        DECLARE @NextId INT = 0;
        DECLARE @IsReturnFlag BIT = 0;
        DECLARE @CodeCompletedCount INT = 0;
        DECLARE @QaCompletedCount INT = 0;
        DECLARE @TotalDocCount INT = 0;

        If @UserTask='QA'
        BEGIN
            EXEC ExecCodingSessionDetails 
           @DocumentID,@CodingSessionDetails,@IsReturn=@IsExecCodingSessionDetails OUTPUT;

            -- Retrieve total document count
            SELECT @TotalDocCount = COUNT(Id) FROM ImportPages;
            SET @Description='Total Document - '+@TotalDocCount;

            -- Retrieve QA completed document count
            SELECT @QaCompletedCount = COUNT(Id) FROM ImportPages WHERE Coded = 1 AND 
Revision = 1;
            SET @Description=CONCAT(@Description,' / Total QA Doc ompleted - 
'+@QaCompletedCount);

        END
        ELSE
        BEGIN
              -- Retrieve total document count and code completed document count
             SELECT @TotalDocCount = COUNT(Id), @CodeCompletedCount = SUM(CASE WHEN Coded = 1 
THEN 1 ELSE 0 END)
             FROM ImportPages;
             SET @Description=CONCAT(@Description,' / Total Coded Doc ompleted - 
'+@CodeCompletedCount);
        END

        DECLARE @LstDocumentId NVARCHAR(255);
        DECLARE @LstId INT;

        -- Get the document by its ID
        SELECT @LstDocumentId = Document_ID,@LstId=Id
        FROM ImportPages
        WHERE Id = @DocOrgId; 

        SET @Description=CONCAT(@Description,' / 1-Last Doc ID - '+@LstDocumentId+', '+'Last 
Id - '+@LstId);

         IF @LstDocumentId IS NULL
          BEGIN
            -- If document does not exist, check for the last document
            SELECT @IsReturnFlag = 
            CASE 
                WHEN @UserTask = 'Coder' AND @TotalDocCount = @CodeCompletedCount THEN 1
                WHEN @UserTask = 'QA' AND @TotalDocCount = @QaCompletedCount THEN 1
                ELSE 0
            END;

            SET @Description=CONCAT(@Description,' / Is Return Flag - '+CAST(@IsReturnFlag AS 
nvarchar(10)));

          END
          ELSE
          BEGIN
             IF @isHistoryDocument=0
             BEGIN
                /*Get Next Available Document*/

                DECLARE @IsAssignedSameDoc BIT;

                -- Check if the document is assigned
                SELECT @IsAssignedSameDoc = CASE WHEN EXISTS (SELECT 1 FROM CheckDocuments 
WHERE Document_ID = @DocumentId) THEN 1 ELSE 0 END;

                IF @IsAssignedSameDoc = 0
                BEGIN
                    -- If not assigned, set next document ID and ID to the current document
                    SET @NextDocumentId = @LstDocumentId;
                    SET @NextId = @LstId;

                    SET @Description=CONCAT(@Description,' / IsAssignedSameDoc - '+ 
 Cast(@IsAssignedSameDoc AS NVARCHAR(10))+', NextDocumentId'+@NextDocumentId+', 
NextId'+@NextId);
            
                END
                ELSE
                BEGIN
                        -- If assigned, find the next available document
                        DECLARE @AvailableDocumentId NVARCHAR(100);
                        DECLARE @AvailableId INT;

                        -- Get the list of documents assigned to the same task
                        WITH AssignedDocs AS (
                            SELECT Document_ID
                            FROM CheckDocuments
                            WHERE UserTask = @UserTask
                        )
                        SELECT TOP 1 @AvailableDocumentId = Document_ID,@AvailableId=Id
                        FROM ImportPages
                        WHERE Document_ID NOT IN (SELECT Document_ID FROM AssignedDocs)
                        ORDER BY Document_ID;

                        IF @AvailableDocumentId IS NOT NULL
                        BEGIN
                            -- If available document found, set its ID as next document ID
                            SET @NextDocumentId = @AvailableDocumentId;
                            SET @NextId = @AvailableId;

                            SET @Description=CONCAT(@Description,' / @AvailableDocumentId - 
'+ @AvailableDocumentId+', NextDocumentId'+@NextDocumentId+', NextId'+@NextId);

                        END
                        ELSE
                        BEGIN
                        -- If not assigned, set next document ID and ID to the current 
document
                            SET @NextDocumentId = @LstDocumentId;
                            SET @NextId = @LstId;

                            SET @Description=CONCAT(@Description,' / ELSE - 
NextDocumentId'+@NextDocumentId+', NextId'+@NextId);

                        END
                    END

                /*Remove Document from CheckDocument Table*/

                 -- Check if the document exists in CheckDocuments table
                IF EXISTS (
                    SELECT 1 
                    FROM CheckDocuments 
                    WHERE Document_ID = @DocumentID 
                    AND ProjectId = @ProjectId 
                    AND UserTask = @UserTask 
                    AND DocumentStatus = 0
                )
                BEGIN
                    -- Remove the document from CheckDocuments table
                    DELETE FROM CheckDocuments 
                    WHERE Document_ID = @DocumentID 
                    AND ProjectId = @ProjectId 
                    AND UserTask = @UserTask 
                    AND DocumentStatus = 0;

                    SET @Description=CONCAT(@Description,'Removed Document from check 
table');
                    -- Output informational message
                    PRINT 'Removed Document from check table: ' + @DocumentID;

                    -- Output informational message
                    PRINT 'Check Table Data Removed: ' + @DocumentID;
                END

            END
             ELSE
             BEGIN
                    SET @NextDocumentId = @LstDocumentId;
                    SET @NextId = @LstId;

                    SET @Description=CONCAT(@Description,' / Outer ELSE - 
NextDocumentId'+@NextDocumentId+', NextId'+@NextId);

             END

        END
      
          PRINT '@nextId - '+ Cast(@nextId As VARCHAR(50));
          
        SET @Description=CONCAT(@Description,' / Next button success');

        EXEC [dbo].[InsertSaveDocAndpartiesLogs] @DocumentID,@Description,0;

          SET @ReturnJSONResult = 
            CASE 
                WHEN @isReturnFlag = 1 THEN N'{"Response": "LastDocument", "Message": "All 
the documents are completed."}'
                WHEN @nextId > 0 THEN N'{"Response": "success", "nextDocumentId": "' + 
@nextDocumentId + '", "nextId": ' + CAST(@nextId AS NVARCHAR(10)) + ', "Message": "Document 
updated successfully"}'
                ELSE N'{"Response": "No documents to code", "Statuscode": 404}'
            END;

        -- Output informational message
        PRINT 'JsonData: ' + @ReturnJSONResult;

        SELECT @ReturnJSONResult As JsonResponse;

        COMMIT TRANSACTION; PRINT 'COMMIT';
    
    END TRY
    BEGIN CATCH
      
        IF @@TRANCOUNT > 0
        BEGIN
            IF @IsAddPartiesAndPartiesHistory = 0 OR @IsAddCodedData=0 OR 
  @IsUpdateImportPages=0
                OR @IsUpdateAdminRegeTitle=0 OR @IsUpdateExportExtras=0 OR @ 
   @IsInsUpdFieldAdminValidations=0
                OR @IsExecCodingSessionDetails=0
                ROLLBACK TRANSACTION MySavepoint; -- Rollback to savepoint
            ELSE
                ROLLBACK TRANSACTION; -- Rollback entire transaction
        END

            -- Get error details
            SELECT 
                @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

            SET @Description=CONCAT(@Description,' / '+@ErrorMessage);

            EXEC [dbo].[InsertSaveDocAndpartiesLogs] @DocumentID,@Description,0;

            -- Set jsonResponse based on error
            IF @ErrorMessage = 'Sequence contains more than one element'
            BEGIN
                SET @ReturnJSONResult = N'{"Response": "Document Saving fail", "Statuscode": 
500}';
            END
            ELSE
            BEGIN
                IF @ErrorState = 1205
                BEGIN
                    SET @ReturnJSONResult = N'{"Response": "1205 - Deadlock Detected", 
"Statuscode": 500}';
                END
                ELSE BEGIN
                   SET @ReturnJSONResult = N'{"Response": "' + @ErrorMessage + 
'","AddPartiesAndPartiesHistor":'+CAST(@IsAddPartiesAndPartiesHistory AS NVARCHAR(10))+'", 
                   "AddCodedData":'+CAST(@IsAddCodedData AS 
NVARCHAR(10))+'","UpdateImportPages":'+CAST(@IsUpdateImportPages AS NVARCHAR(10))+'",
                   "UpdateAdminRegeTitle":'+CAST(@IsUpdateAdminRegeTitle AS 
NVARCHAR(10))+'","UpdateExportExtras":'+CAST(@IsUpdateExportExtras AS NVARCHAR(10))+'",
                   "InsUpdFieldAdminValidations":'+CAST(@IsInsUpdFieldAdminValidations AS 
NVARCHAR(10))+'",
                   "ExecCodingSessionDetails":'+CAST(@IsExecCodingSessionDetails AS 
NVARCHAR(10))+'","Statuscode": 500}'; END;
            END

            SELECT @ReturnJSONResult As JsonResponse;

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;

   END;

END;

Rest are nested procedures

ALTER PROCEDURE [dbo].[AddCodedData] 
                    (
                        @DocumentID VARCHAR(50) = NULL,
                        @ProjectId VARCHAR(50) = NULL,
                        @DocumentDate NVARCHAR(255) = NULL,
                        @DocumentType NVARCHAR(255) = NULL,
                        @EnteredById INT = NULL,
                        @Estimated NVARCHAR(255) = NULL,
                        @Title NVARCHAR(4000) = NULL,
                        @CodingQATime INT = NULL,
                        @IsCorrected INT = NULL,
                        @UserTask VARCHAR(10) = NULL,
                        @IsReturn BIT OUTPUT
                    )
                    AS
                    BEGIN
                        SET NOCOUNT ON;

                        DECLARE @ErrorMessage NVARCHAR(1000);
                        DECLARE @ErrorSeverity INT;
                        DECLARE @ErrorState INT;

                        Declare @Description NVARCHAR(MAX);

                        BEGIN TRANSACTION;
                         BEGIN TRY

                            SET @IsReturn=0;

                               DECLARE @TempCodedDatas AS TABLE (
                               [Document_ID] NVARCHAR(255), [Image_File_Name] NVARCHAR(500), 
   [page_label] NVARCHAR(255), [page_num] INT, [num_pages] INT,
                               [Coded] INT, [Revision] INT, [DocType] NVARCHAR(255), 
   [EnteredBy] INT, [HostDocId] NVARCHAR(255),
                               [ExportDate] DATETIME, [ImportDate] DATETIME, [Percentage] 
    INT, [SetId] INT, [DateCreated] DATETIME, 
                               LastModified DATETIME, Host_Reference NVARCHAR(255), 
  [Document_Date] DATETIME, [Estimated] NVARCHAR(255),
                               [Document_Type] NVARCHAR(255),Title NVARCHAR(4000), 
   Document_DateValue NVARCHAR(255),
                               [CodingDate] DATETIME, [CodingTime] INT,[QADate] DATETIME, 
  [QATime] INT, [IsCorrected] INT
                            );

    
                                    INSERT inTO @TempCodedDatas ([Document_ID], 
   [Image_File_Name], [page_label], [page_num], [num_pages], [Coded], [Revision], [DocType], 
   [EnteredBy], [HostDocId],
                                    [ExportDate], [ImportDate], [Percentage], [SetId], 
  [DateCreated], [LastModified], [Host_Reference], [Document_Date], [Estimated], 
  [Document_Type],
                                    [Title], [Document_DateValue], [CodingDate],[QADate], 
  [CodingTime],[QATime], [IsCorrected])
                                    SELECT IPS.[Document_ID],IPS.[Image_File_Name],IPS. 
 [page_label],IPS.[page_num],IPS.[num_pages],1 AS [Coded], 
                                    (CASE WHEN @UserTask='Coder' THEN 0 ELSE 1 END) AS 
 [Revision],@DocumentType AS [DocType],@EnteredById AS [EnteredBy], 
                                    IPS.[HostDocId],IPS.[ExportDate],IPS.[ImportDate],IPS. 
  [Percentage],IPS.[SetId],IPS.[DateCreated],GETDATE() AS [LastModified], 
                                    ICD.[Host_Reference], 
                                    CASE 
                                        WHEN ISNULL(@DocumentDate, '') = '' THEN NULL 
                                        ELSE CONVERT(DATETIME, @DocumentDate) 
                                    END AS [DocDate], 
                                    @Estimated AS [Estimated], 
                                    @DocumentType AS [Document_Type], 
                                    CASE 
                                        WHEN LEN(@Title) > 0 THEN @Title 
                                        ELSE 'Untitled'
                                    END AS [Title], 
                                    CASE 
                                        WHEN ISNULL(ICD.Document_DateValue, '') = '' THEN 
  NULL 
                                        ELSE CONVERT(DATETIME, ICD.Document_DateValue) 
                                    END AS [Document_DateValue], 
                                    (CASE WHEN @UserTask='Coder' THEN GETDATE() ELSE NULL 
  END) AS [CodingDate],
                                    (CASE WHEN @UserTask='Coder' THEN NULL ELSE GETDATE() 
  END) AS [QADate], 
                                    (CASE WHEN @UserTask='Coder' THEN @CodingQATime ELSE 0 
  END) AS [CodingTime], 
                                    (CASE WHEN @UserTask='Coder' THEN 0 ELSE @CodingQATime 
 END) AS [CodingTime], 
                                    @IsCorrected AS [IsCorrected]
                                    FROM
                                        ImportPages IPS
                                    INNER JOIN 
                                        ImportCodedDatas ICD ON ICD.Document_ID = 
 IPS.Document_ID
                                    WHERE 
                                        IPS.Document_ID = @DocumentID


                             IF @UserTask = 'Coder'
                                BEGIN

                                    INSERT INTO CodedDatas ([Document_ID], [Image_File_Name], 
   [page_label], [page_num], [num_pages], [Coded], [Revision],
                                    [DocType], [EnteredBy], [HostDocId],[ExportDate], 
   [ImportDate], [Percentage], [SetId], [DateCreated], [LastModified],
                                    [main_id],[End_Page],[No_Pages],[Host_Reference], 
  [Document_Date], [Estimated], [Document_Type], [Title],
                                    [Document_DateValue], [CodingDate], [QADate], 
  [CodingTime], [QATime], [IsCorrected],[CodingStatus],[QAStatus])
                                    SELECT [Document_ID], [Image_File_Name], [page_label], 
  [page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
                                    [ExportDate], [ImportDate], [Percentage], [SetId], 
 [DateCreated], [LastModified],NULL,NULL,0, Host_Reference,
                                    [Document_Date], [Estimated], [DocType],Title, 
 [Document_DateValue],[CodingDate], [QADate], [CodingTime], [QATime],
                                    [IsCorrected],NULL,NULL
                                    FROM @TempCodedDatas

                                    SET @Description='Coded Data Saved';
                                END
                                ELSE
                                BEGIN

                                    INSERT INTO CodedDatas ([Document_ID], [Image_File_Name], 
    [page_label], [page_num], [num_pages], [Coded], [Revision],
                                    [DocType], [EnteredBy], [HostDocId],[ExportDate], 
   [ImportDate], [Percentage], [SetId], [DateCreated], [LastModified],
                                        [main_id],[End_Page],[No_Pages],[Host_Reference], 
   [Document_Date], [Estimated], [Document_Type], [Title],
                                    [Document_DateValue], [CodingDate], [QADate], 
  [CodingTime], [QATime], [IsCorrected],[CodingStatus],[QAStatus])
                                    SELECT [Document_ID], [Image_File_Name], [page_label], 
 [page_num], [num_pages],[Coded], [Revision], [DocType], [EnteredBy], [HostDocId],
                                    [ExportDate], [ImportDate], [Percentage], [SetId], 
 [DateCreated], [LastModified],NULL,NULL,0, Host_Reference,
                                    [Document_Date], [Estimated], [DocType],Title, 
 [Document_DateValue],[CodingDate], [QADate], [CodingTime], [QATime],
                                    [IsCorrected],NULL,NULL
                                    FROM @TempCodedDatas

                                    SET @Description='Review data Saved';
                                END;
                                
                            EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
   @DocumentID,@Description,1;

                            COMMIT TRANSACTION;

                            SET @IsReturn=1;

                         END TRY
                            BEGIN CATCH
                                IF @@TRANCOUNT > 0
                                    ROLLBACK TRANSACTION;

                                -- Get error details
                                SELECT 
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                SET @Description=@ErrorMessage;
                                EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
 @DocumentID,@Description,0;

                                SET @IsReturn=0;
                            END CATCH;
                            
                         END

 
    ALTER PROCEDURE [dbo].[UpdateAdminRegeTitle] 
                                (
                                    @DocumentID VARCHAR(50) = NULL,
                                    @DocumentType NVARCHAR(255) = NULL,
                                    @EnteredById INT = NULL,
                                    @Title NVARCHAR(4000) = NULL,
                                    @DocumentDate NVARCHAR(255) = NULL,
                                    @Estimated NVARCHAR(255) = NULL,
                                    @UserTask VARCHAR(10) = NULL,
                                    @IsReturn BIT OUTPUT
                                )
                                AS
                                BEGIN
                                    SET NOCOUNT ON;

                                    DECLARE @ErrorMessage NVARCHAR(1000);
                                    DECLARE @ErrorSeverity INT;
                                    DECLARE @ErrorState INT;

                                    Declare @Description NVARCHAR(MAX);

                                    BEGIN TRANSACTION;
                                     BEGIN TRY

                                        SET @IsReturn=0;

                                        /*Update Admin Regex*/

                                            DECLARE @AdminRegexCount INT;
                                            DECLARE @CodedDataCount INT;

                                            -- Get the count of enabled admin regexes
                                            SELECT @AdminRegexCount = COUNT(Id)
                                            FROM AdminRegexs
                                            WHERE Enabled = 1;

                                            -- Get the latest coded data for the document
                                            SELECT @CodedDataCount = COUNT(Id)
                                            FROM CodedDatas --WITH (UPDLOCK, SERIALIZABLE)
                                            WHERE Document_ID = @DocumentID;

                                            -- Fetch the latest coded data
                                            DECLARE @LatestCodedData TABLE (
                                                Id INT,
                                                Title NVARCHAR(MAX)
                                            );

                                            INSERT INTO @LatestCodedData (Id, Title)
                                            SELECT TOP 1 Id, Title
                                            FROM CodedDatas --WITH (UPDLOCK, SERIALIZABLE)
                                            WHERE Document_ID = @DocumentID
                                            ORDER BY LastModified DESC;

                                            Declare @CodedDataTitle NVarchar(255)
                                            Set @CodedDataTitle=(SELECT Title FROM 
  @LatestCodedData)

                                            -- Apply common updates conditionally
                                            IF (@CodedDataTitle) != 'Untitled'
                                            BEGIN
                                                UPDATE ImportCodedDatas
                                                SET Document_Date = @DocumentDate,
                                                    Estimated = @Estimated,
                                                    Document_Type = @DocumentType
                                                WHERE Document_ID = @DocumentID;
                                            END;

                                            DECLARE @TitleString NVARCHAR(MAX);
                                            DECLARE @Replacement NVARCHAR(MAX);

                                            -- Apply title regex replacements
                                            IF @AdminRegexCount > 0 AND @CodedDataCount > 0
                                            BEGIN
                                                -- Loop through admin regexes
                                                DECLARE @Index INT = 1;

                                                WHILE @Index <= @AdminRegexCount
                                                BEGIN
                                                    -- Get the current admin regex and 
  replacement
                                                    SELECT @TitleString = Matchexpression, 
 @Replacement = Replacement
                                                    FROM (
                                                        SELECT Matchexpression, Replacement, 
 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
                                                        FROM AdminRegexs
                                                    ) AS AdminRegex
                                                    WHERE RowNum = @Index;

                                                    -- Update titles based on admin regex
                                                    IF @TitleString = '(Proprietary 
  Limited)+/g'
                                                        AND NOT EXISTS (SELECT 1 FROM 
  @LatestCodedData WHERE Title LIKE '%(Proprietary Limited)+/g%')
                                                    BEGIN
                                                        SET @TitleString = 
 REPLACE(REPLACE(REPLACE(@TitleString, '(', ''), ')', ''), '+/g', '');
                                                    END
                                                    ELSE
                                                    BEGIN
                                                        SET @TitleString = 
 REPLACE(REPLACE(REPLACE(REPLACE(@TitleString, '[', ''), ']', ''), '+/g', ''), '\\', '\');

                                                        SET @Title = REPLACE(CASE WHEN 
 @TitleString LIKE '%[,.;:()!?]+/g%' 
                                                                     THEN 
 REPLACE(CAST(REPLACE(@Title, @TitleString, @Replacement) AS NVARCHAR(MAX)), 
                                                                     '[' + @TitleString + 
  ']', @Replacement) ELSE @Title END, @TitleString, @Replacement);
                                                    END

                                                     SET @Title = REPLACE(
                                                        CASE 
                                                            WHEN CHARINDEX(@TitleString, 
 @CodedDataTitle) > 0 THEN 
                                                                CASE 
                                                                    WHEN @CodedDataTitle 
  COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%' + @TitleString + '%' THEN 
                                                                         
  REPLACE(@CodedDataTitle, @TitleString, @Replacement) 
                                                                    ELSE @CodedDataTitle 
                                                                END 
                                                            ELSE @Title 
                                                        END

                                                    , @TitleString, @Replacement);

                                                    -- Update titles in ImportCodedDatas 
  table
                                                    UPDATE ImportCodedDatas
                                                    SET Title = @Title;

                                                    -- Update titles in CodedDatas table
                                                    UPDATE CodedDatas
                                                    SET Title = @Title;

                                                    -- Increment index
                                                    SET @Index = @Index + 1;
                                                END;
    
                                            End;
                                            Else
                                            Begin
                                                 DECLARE @NewTitle NVARCHAR(MAX);

                                                SET @NewTitle = REPLACE(LTRIM(RTRIM(@Title)), 
  ' ', ''); -- Remove leading and trailing spaces

                                                UPDATE ImportCodedDatas
                                                SET Title = CASE 
                                                                WHEN LEN(@NewTitle) > 0 THEN 
  @Title 
                                                                ELSE 'Untitled'
                                                            END
                                                WHERE Document_ID = @DocumentID;
                                            End;

                                        COMMIT TRANSACTION;

                                        SET @Description='Update Regex Title';
                                        
                                        EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
  @DocumentID,@Description,1;

                                        SET @IsReturn=1;

                                     END TRY
                                        BEGIN CATCH
                                            IF @@TRANCOUNT > 0
                                                ROLLBACK TRANSACTION;

                                            -- Get error details
                                            SELECT 
                                                @ErrorMessage = ERROR_MESSAGE(),
                                                @ErrorSeverity = ERROR_SEVERITY(),
                                                @ErrorState = ERROR_STATE();

                                            SET @Description=@ErrorMessage;
                                            EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
 @DocumentID,@Description,0;

                                            SET @IsReturn=0;
                                        END CATCH;


                                END

    ALTER PROCEDURE [dbo].[UpdateExportExtras] 
                                (
                                    @DocumentID VARCHAR(50) = NULL,
                                    @IsReturn BIT OUTPUT
                                )
                                AS
                                BEGIN
                                    SET NOCOUNT ON;

                                    DECLARE @ErrorMessage NVARCHAR(1000);
                                    DECLARE @ErrorSeverity INT;
                                    DECLARE @ErrorState INT;

                                    Declare @Description NVARCHAR(MAX);

                                    BEGIN TRANSACTION;
                                     BEGIN TRY
                                        SET @IsReturn=0;

                                                DECLARE @MatchExpr NVARCHAR(MAX);
                                                DECLARE @Replacement NVARCHAR(MAX);
                                                DECLARE @UpdatedCount INT;

                                                -- Get Export_extras matching the Document_ID
                                                SELECT 
                                                    @MatchExpr = Matchexpression,
                                                    @Replacement = Replacement
                                                FROM 
                                                    AdminRegexs
                                                WHERE 
                                                    Enabled = 1;

                                                -- Update Export_extras for each AdminRegex
                                                IF @MatchExpr IS NOT NULL AND @Replacement IS 
 NOT NULL
                                                BEGIN
                                                    -- Update theValue column
                                                    UPDATE Export_extras
                                                    SET theValue = REPLACE(theValue, 
 @MatchExpr, @Replacement)
                                                    WHERE Document_ID = @DocumentID AND 
 theValue IS NOT NULL;

                                                    SET @UpdatedCount = @@ROWCOUNT;

                                                    PRINT CONCAT('Updated ', @UpdatedCount, ' 
 Export_extras (theValue) for AdminRegex: ', @MatchExpr, '. Document ID: ', @DocumentID);

                                                    -- Update memoValue column
                                                    UPDATE Export_extras
                                                    SET memoValue = REPLACE(memoValue, 
  @MatchExpr, @Replacement)
                                                    WHERE Document_ID = @DocumentID AND 
  memoValue IS NOT NULL;

                                                    SET @UpdatedCount = @@ROWCOUNT;

                                                    SET @Description='Updated'+ 
  @UpdatedCount+ ' Export_extras (memoValue) for AdminRegex: '+ @MatchExpr;
                                                    PRINT CONCAT('Updated', @UpdatedCount, ' 
  Export_extras (memoValue) for AdminRegex: ', @MatchExpr, '. Document ID: ', @DocumentID);

                                                    -- Update textValue column
                                                    UPDATE Export_extras
                                                    SET textValue = REPLACE(textValue, 
  @MatchExpr, @Replacement)
                                                    WHERE Document_ID = @DocumentID AND 
 textValue IS NOT NULL;

                                                    SET @UpdatedCount = @@ROWCOUNT;
                                                    
                                                    SET @Description='Updated '+ 
  @UpdatedCount+ ' Export_extras (textValue) for AdminRegex: '+ @MatchExpr ;
                                                    PRINT CONCAT('Updated ', @UpdatedCount, ' 
 Export_extras (textValue) for AdminRegex: ', @MatchExpr, '. Document ID: ', @DocumentID);
                                                END;

                                                EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
 @DocumentID,@Description,1;

                                        COMMIT TRANSACTION;

                                        SET @IsReturn=1;

                                     END TRY
                                        BEGIN CATCH
                                            IF @@TRANCOUNT > 0
                                                ROLLBACK TRANSACTION;

                                            -- Get error details
                                            SELECT 
                                                @ErrorMessage = ERROR_MESSAGE(),
                                                @ErrorSeverity = ERROR_SEVERITY(),
                                                @ErrorState = ERROR_STATE();

                                            SET @Description=@ErrorMessage;
                                            EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
 @DocumentID,@Description,0;

                                            SET @IsReturn=0;
                                        END CATCH;

                                END

    ALTER PROCEDURE [dbo].[UpdateImportPages] 
                                (
                                    @DocumentID VARCHAR(50) = NULL,
                                    @DocumentType NVARCHAR(255) = NULL,
                                    @EnteredById INT = NULL,
                                    @UserTask VARCHAR(10) = NULL,
                                    @IsReturn BIT OUTPUT
                                )
                                AS
                                BEGIN
                                    SET NOCOUNT ON;

                                    DECLARE @ErrorMessage NVARCHAR(1000);
                                    DECLARE @ErrorSeverity INT;
                                    DECLARE @ErrorState INT;

                                    Declare @Description NVARCHAR(MAX);

                                    BEGIN TRANSACTION;
                                     BEGIN TRY

                                        SET @IsReturn=0;

                                           /*Updating ImportPage*/

                                        IF @UserTask = 'Coder'
                                                BEGIN
                                                    UPDATE ImportPages 
                                                    SET Coded = 1,
                                                        EnteredBy = @EnteredById,
                                                        DocType = @DocumentType,
                                                        LastModified = GETDATE()
                                                    WHERE Document_ID = @DocumentID;
                                                END
                                                ELSE
                                                BEGIN
                                                    UPDATE ImportPages 
                                                    SET Revision = 1,
                                                        EnteredBy = @EnteredById,
                                                        LastModified = GETDATE()
                                                    WHERE Document_ID = @DocumentID;
                                                END;
                                                
                                        SET @Description='Updated Import Pages';
                                        
                                        EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
 @DocumentID,@Description,1;

                                        COMMIT TRANSACTION;

                                        SET @IsReturn=1;

                                     END TRY
                                        BEGIN CATCH
                                            IF @@TRANCOUNT > 0
                                                ROLLBACK TRANSACTION;

                                            -- Get error details
                                            SELECT 
                                                @ErrorMessage = ERROR_MESSAGE(),
                                                @ErrorSeverity = ERROR_SEVERITY(),
                                                @ErrorState = ERROR_STATE();

                                            SET @Description=@ErrorMessage;
                                           EXEC [dbo].[InsertSaveDocAndpartiesLogs] 
 @DocumentID,@Description,0;

                                            SET @IsReturn=0;
                                        END CATCH;


                                END

olafhelper · ‎Apr 29 2024

"Thanks" for posting those tons of SQL code.
We don't have your database to test it, you have to dig deeper into it on your own.

Products (50)

Special Topics (27)

Video Hub (462)

Most Active Hubs

Most Active Hubs

Video Hub

Data is Null. The method or property cannot be call on null values. Exception

Data is Null. The method or property cannot be call on null values. Exception

Re: Data is Null. The method or property cannot be call on null values. Exception