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