SP Example for JSON object

 USE [AGSBIM_APPS_DEV]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [AGS_BIM_GLL].[USP_SAVE_USER_DELEGATION](

@employeeId NVARCHAR(10),

@action NVARCHAR(10),

@requestObj NVARCHAR(MAX)

)

AS

BEGIN

DECLARE @id INT, @targetEmployeeId NVARCHAR(10), @startDate NVARCHAR(50), @endDate NVARCHAR(50), @reason NVARCHAR(MAX);

SELECT @id = id, @targetEmployeeId = targetEmployeeId, @startDate = startDate, @endDate = endDate, @reason = reason 

FROM OPENJSON(@requestObj) WITH

(  

        id INT '$.id',

targetEmployeeId NVARCHAR(10) '$.targetEmployeeId',

        startDate NVARCHAR(50) '$.startDate',

        endDate NVARCHAR(50) '$.endDate',

        reason NVARCHAR(MAX) '$.reason'

    )

    DECLARE @count INT;

   

IF (@action = 'NEW')

BEGIN

SELECT @count = COUNT(*) FROM [AGS_BIM_GLL].[user_delegation] WHERE source_employee_id = @employeeId AND  active = 1

AND ((@startDate BETWEEN start_date AND end_date) OR (@endDate BETWEEN start_date AND end_date))

IF(@count > 0)

BEGIN

RAISERROR('Delegation was done for this time period already', 16, 1)

RETURN

END

INSERT INTO [AGS_BIM_GLL].[user_delegation](source_employee_id, target_employee_id, start_date, end_date, active, created_at, updated_at)

SELECT @employeeId, @targetEmployeeId, @startDate, @endDate, 1, GETUTCDATE(), GETUTCDATE()

END

ELSE IF (@action = 'UPDATE')

BEGIN

SELECT @count = COUNT(*) FROM [AGS_BIM_GLL].[user_delegation] WHERE source_employee_id = @employeeId AND  active = 1

AND ((@startDate BETWEEN start_date AND end_date) OR (@endDate BETWEEN start_date AND end_date))

AND id != @id

IF(@count > 0)

BEGIN

RAISERROR('Delegation was done for this time period already', 16, 1)

RETURN

END

UPDATE [AGS_BIM_GLL].[user_delegation] SET target_employee_id = @targetEmployeeId, start_date = @startDate, end_date = @endDate, updated_at = GETUTCDATE()

WHERE id = @id

END

ELSE IF (@action = 'CANCEL')

BEGIN

UPDATE [AGS_BIM_GLL].[user_delegation] SET reason = @reason, active = 0, updated_at = GETUTCDATE()

WHERE id = @id

END

END

Method for Managing Sharepoint online throttling for http status code 429 and 503, "Response status code does not indicate success: 429 ()."

 public static void ExecuteQueryWithIncrementalRetry(this ClientContext clientContext, int retryCount, int delay)

{ int retryAttempts = 0; int backoffInterval = delay; int retryAfterInterval = 0; bool retry = false; ClientRequestWrapper wrapper = null; if (retryCount <= 0) throw new ArgumentException("Provide a retry count greater than zero."); if (delay <= 0) throw new ArgumentException("Provide a delay greater than zero."); // Do while retry attempt is less than retry count while (retryAttempts < retryCount) { try { if (!retry) { clientContext.ExecuteQuery(); return; } else { //increment the retry count retryAttempts++; // retry the previous request using wrapper if (wrapper != null && wrapper.Value != null) { clientContext.RetryQuery(wrapper.Value); return; } // retry the previous request as normal else { clientContext.ExecuteQuery(); return; } } } catch (WebException ex) { var response = ex.Response as HttpWebResponse; // Check if request was throttled - http status code 429 // Check is request failed due to server unavailable - http status code 503 if (response != null && (response.StatusCode == (HttpStatusCode)429 || response.StatusCode == (HttpStatusCode)503)) { wrapper = (ClientRequestWrapper)ex.Data["ClientRequest"]; retry = true; // Determine the retry after value - use the `Retry-After` header when available string retryAfterHeader = response.GetResponseHeader("Retry-After"); if (!string.IsNullOrEmpty(retryAfterHeader)) { if (!Int32.TryParse(retryAfterHeader, out retryAfterInterval)) { retryAfterInterval = backoffInterval; } } else { retryAfterInterval = backoffInterval; } // Delay for the requested seconds Thread.Sleep(retryAfterInterval * 1000); // Increase counters backoffInterval = backoffInterval * 2; } else { throw; } } } throw new MaximumRetryAttemptedException($"Maximum retry attempts {retryCount}, has be attempted."); } [Serializable] public class MaximumRetryAttemptedException : Exception { public MaximumRetryAttemptedException(string message) : base(message) { } }

Insert SP from Json Object

 USE [AGSBIM_APPS_DEV]

GO

/****** Object:  StoredProcedure [AGS_BIM_GLL].[USP_SAVE_LOANOUT_REQUEST]    Script Date: 3/28/2023 3:43:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [AGS_BIM_GLL].[USP_SAVE_LOANOUT_REQUEST](

@requestedEmployeeId NVARCHAR(10),

@team NVARCHAR(50),

@requestObj VARCHAR(MAX)

)

AS

BEGIN

BEGIN TRANSACTION  

BEGIN TRY

DECLARE @requestType NVARCHAR(10) = 'LOAN_OUT';

DECLARE @count NVARCHAR(10);  

SET @count = (SELECT COUNT(*) FROM [AGS_BIM_GLL].[request]) + 1

DECLARE @requestId NVARCHAR(10);  

SET @requestId = CONCAT('REQ', REPLICATE('0',7-LEN(@count)) + @count);

-- status id 1: pending for approval, status id 2: approved

DECLARE @defaultStatusId INT;

SET @defaultStatusId = CASE 

WHEN ((SELECT COUNT(*) FROM [AGS_BIM_GLL].[team_configuration] WHERE team = @team AND request_type =  @requestType AND auto_approve = 1)) > 0 THEN 2

ELSE 1

END

INSERT INTO [AGS_BIM_GLL].[request] (request_id, request_type, status_id, created_by, created_at, updated_at ) VALUES

(@requestId, @requestType, @defaultStatusId, @requestedEmployeeId, GETUTCDATE(), GETUTCDATE());

INSERT INTO [AGS_BIM_GLL].[available_resource_pool] (request_id, team, employee_id, justification, start_date, end_date, status_id , created_at, updated_at)

SELECT @requestId, team, employee_id, justification, start_date, end_date, @defaultStatusId, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

team NVARCHAR(50) '$.team', 

employee_id NVARCHAR(10) '$.employeeId', 

            justification NVARCHAR(MAX) '$.justification',

            start_date DATE '$.startDate', 

            end_date DATE '$.endDate'

) 

DECLARE @listOfEmployeeIds table (employeeId NVARCHAR(10));

INSERT INTO @listOfEmployeeIds

SELECT employee_id FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId'

)

-- Skills

DELETE FROM [AGS_BIM_GLL].[employee_skills_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_skills_mapping] (employee_id, skill_name, created_at, updated_at)

SELECT employee_id, skill_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        skills NVARCHAR(MAX) '$.skills' as JSON

)  

CROSS APPLY OPENJSON (skills) WITH

(

id INT '$.id',  

skill_name NVARCHAR(255) '$.name'

)

-- Languages

DELETE FROM [AGS_BIM_GLL].[employee_languages_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_languages_mapping] (employee_id, language_name, created_at, updated_at)

SELECT employee_id, language_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        languages NVARCHAR(MAX) '$.languages' as JSON

)  

CROSS APPLY OPENJSON (languages) WITH

(

id INT '$.id',  

language_name NVARCHAR(255) '$.name'

)

-- Certifications

DELETE FROM [AGS_BIM_GLL].[employee_certifications_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_certifications_mapping] (employee_id, certification_name, created_at, updated_at)

SELECT employee_id, certification_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        certifications NVARCHAR(MAX) '$.certifications' as JSON

)  

CROSS APPLY OPENJSON (certifications) WITH

(

id INT '$.id',  

certification_name NVARCHAR(255) '$.name'

)

-- Passport Details

DELETE FROM [AGS_BIM_GLL].[employee_passport_details] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_passport_details] (employee_id, issued_by, valid_from, valid_to, created_at, updated_at)

SELECT employee_id, issued_by, valid_from, valid_to, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        passportDetails NVARCHAR(MAX) '$.passportDetails' as JSON

)  

CROSS APPLY OPENJSON (passportDetails) WITH

(

id INT '$.id',  

issued_by INT '$.issued_by',

valid_from DATE '$.valid_from',

valid_to DATE '$.valid_to'

)

-- Visa Details

DELETE FROM [AGS_BIM_GLL].[employee_visa_details] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_visa_details] (employee_id, country_id, visa_type_id, valid_from, valid_to, created_at, updated_at)

SELECT employee_id, country_id, visa_type_id, valid_from, valid_to, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        visaDetails NVARCHAR(MAX) '$.visaDetails' as JSON

)  

CROSS APPLY OPENJSON (visaDetails) WITH

(

id INT '$.id',  

country_id INT '$.country_id',

visa_type_id INT '$.visa_type_id',

valid_from DATE '$.valid_from',

valid_to DATE '$.valid_to'

)

COMMIT TRANSACTION

END TRY

BEGIN CATCH

  THROW(SELECT ERROR_MESSAGE());

END CATCH

END