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
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 Read More