Forum Discussion
Data is Null. The method or property cannot be call on null values. Exception
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
1 Reply
- "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.
