DevQt's questions

Estou com um problema com meu script T-SQL. Estou pensando e trabalhando em como produzir a saída esperada dele.

Aqui está meu script T-SQL:

DECLARE @user_id VARCHAR(50) = 'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')

SELECT
    orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
    oa_prod.nested_data AS nested_prod,
    cnb.amount_to_pay
FROM
    [testDB].[dbo].[Orders] AS orders
RIGHT OUTER JOIN
    [testDB].[dbo].[Payment] AS cnb
ON
    orders.order_id = cnb.order_id
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
    SELECT
        orders.batch_code,
        orders.order_id,
        prod.prod_name,
        pv_kn.key_name,
        pv_kv.value,
        CASE WHEN
            orders.prod_id IS NOT NULL
        THEN
            prod.disc_price
        WHEN
            orders.prod_var_id IS NOT NULL
        THEN
            prod_var.disc_price
        END AS disc_price,
        orders.quantity
    FROM
        [testDB].[dbo].[Product] AS prod
    RIGHT OUTER JOIN
        [testDB].[dbo].[SubProduct] AS prod_var
    ON
        prod.prod_id = prod_var.prod_id
    LEFT OUTER JOIN
        [testDB].[dbo].[SubProductVarKeyValue] AS pv_kv
    ON
        prod_var.prod_var_id = pv_kv.prod_var_id
    LEFT OUTER JOIN
        [testDB].[dbo].[SubProductVarKeyNames] AS pv_kn
    ON
        pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
    WHERE       
        prod.prod_id = orders.prod_id
        OR prod_var.prod_var_id = orders.prod_var_id
    FOR JSON PATH,
    INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
    orders.disable = 0
    AND cnb.disable = 0
    AND orders.user_id = @user_id
    AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
    orders.dt_stamp DESC,
    orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

Aqui está meu exemplo mínimo e reproduzível usando variáveis ​​de tabela:

DECLARE @StatusRef TABLE(
    stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
    com_usd_wrds NVARCHAR(100) NOT NULL
);

DECLARE @Product TABLE(
    prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
    prod_name VARCHAR(200) NOT NULL,
    stock INT NOT NULL,
    disc_price DECIMAL(12, 2) NOT NULL
);

DECLARE @SubProduct TABLE(
    prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
    stock INT NOT NULL,
    disc_price DECIMAL(12, 2) NOT NULL,
    prod_id VARCHAR(50) NOT NULL
);

DECLARE @Orders TABLE(
    order_id VARCHAR(50) PRIMARY KEY NOT NULL,
    batch_code VARCHAR(50) NULL,
    quantity INT NOT NULL,
    stat_ref_id VARCHAR(50) NOT NULL,
    disable BIT DEFAULT (0) NOT NULL,
    dt_stamp DATETIME NOT NULL,
    prod_id VARCHAR(50) NULL,
    prod_var_id VARCHAR(50) NULL,
    user_id VARCHAR(50) NOT NULL
);

DECLARE @Payment TABLE(
    amount_to_pay DECIMAL(14, 2) NOT NULL,
    order_id VARCHAR(50) NOT NULL,
    disable BIT DEFAULT (0) NOT NULL
);

DECLARE @SubProductVarKeyValue TABLE(
    value VARCHAR(100) NOT NULL,
    prod_var_id VARCHAR(50) NOT NULL,
    pv_key_name_id VARCHAR(50) NOT NULL
);

DECLARE @SubProductVarKeyNames TABLE(
    pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
    key_name VARCHAR(100) NOT NULL
);

INSERT INTO @StatusRef
VALUES
(
    'STAT-REF-1001', -- stat_ref_id
    'PENDING' -- com_usd_wrds
),
(
    'STAT-REF-1002', -- stat_ref_id
    'APPROVED' -- com_usd_wrds
),
(
    'STAT-REF-1003', -- stat_ref_id
    'PROCESSING' -- com_usd_wrds
);

INSERT INTO @Product
VALUES
(
    'PROD-ID-1001', -- prod_id
    'iPhone', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1002', -- prod_id
    'Samsung', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1003', -- prod_id
    'Nokia', -- prod_name
    75, -- stock
    33150.00 -- disc_price
),
(
    'PROD-ID-1004', -- prod_id
    'Google', -- prod_name
    100, -- stock
    53509.00 -- disc_price
),
(
    'PROD-ID-1005', -- prod_id
    'Sony', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
),
(
    'PROD-ID-1006', -- prod_id
    'Lenovo', -- prod_name
    0, -- stock | dependent to @SubProduct
    0.00 -- disc_price | dependent to @SubProduct
);

INSERT INTO @SubProduct
VALUES
(
    'PROD-VAR-ID-1', -- prod_var_id
    25, -- stock
    45809.00, -- disc_price
    'PROD-ID-1001' -- prod_id
),
(
    'PROD-VAR-ID-2', -- prod_var_id
    50, -- stock
    40209.00, -- disc_price
    'PROD-ID-1002' -- prod_id
),
(
    'PROD-VAR-ID-3', -- prod_var_id
    0, -- stock | dependent to @Product
    0.00, -- disc_price | dependent to @Product
    'PROD-ID-1003' -- prod_id
),
(
    'PROD-VAR-ID-4', -- prod_var_id
    0, -- stock | dependent to @Product
    0.00, -- disc_price | dependent to @Product
    'PROD-ID-1004' -- prod_id
),
(
    'PROD-VAR-ID-5', -- prod_var_id
    125, -- stock
    25809.00, -- disc_price
    'PROD-ID-1005' -- prod_id
),
(
    'PROD-VAR-ID-6', -- prod_var_id
    150, -- stock
    49100.00, -- disc_price
    'PROD-ID-1006' -- prod_id
);

INSERT INTO @SubProductVarKeyValue
VALUES
(
    'new', -- value
    'PROD-VAR-ID-1', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-2', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-5', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
),
(
    'new', -- value
    'PROD-VAR-ID-6', -- prod_var_id
    'PVKN-ID-1' -- pv_key_name_id
)

INSERT INTO @SubProductVarKeyNames
VALUES
(
    'PVKN-ID-1', -- pv_key_name_id
    'Condition' -- key_name
)

INSERT INTO @Orders
(
    order_id,
    batch_code,
    quantity,
    stat_ref_id,
    dt_stamp,
    prod_id,
    prod_var_id,
    user_id
)
VALUES
(
    'ORDER-2025-04-11-B71D0E2F5D8C', -- order_id
    NULL, -- batch_code
    1, -- quantity
    'STAT-REF-1003', -- stat_ref_id
    '2025-04-14 10:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-1', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-11-D95EB033CA40', -- order_id
    'BGUID-2025-04-11-6D81B58FAE94', -- batch_code
    2, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-13 09:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-2', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-11-7F04EFA2BB60', -- order_id
    'BGUID-2025-04-11-6D81B58FAE94', -- batch_code
    2, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-13 09:17:20.963', -- dt_stamp
    'PROD-ID-1003', -- prod_id
    NULL, -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-10-3F03EAA47686', -- order_id
    'BGUID-2025-04-10-20239FD2059F', -- batch_code
    1, -- quantity
    'STAT-REF-1002', -- stat_ref_id
    '2025-04-12 08:17:20.963', -- dt_stamp
    'PROD-ID-1004', -- prod_id
    NULL, -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-10-F4A89E2C4A30', -- order_id
    'BGUID-2025-04-10-20239FD2059F', -- batch_code
    1, -- quantity
    'STAT-REF-1002', -- stat_ref_id
    '2025-04-12 08:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-5', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
    'ORDER-2025-04-08-31BD887341FA', -- order_id
    NULL, -- batch_code
    1, -- quantity
    'STAT-REF-1001', -- stat_ref_id
    '2025-04-11 07:17:20.963', -- dt_stamp
    NULL, -- prod_id
    'PROD-VAR-ID-6', -- prod_var_id
    'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);

INSERT INTO @Payment
(
    amount_to_pay,
    order_id
)
VALUES
(
    45809.00, -- amount_to_pay
    'ORDER-2025-04-11-B71D0E2F5D8C' -- order_id
),
(
    146718.00, -- amount_to_pay
    'ORDER-2025-04-11-D95EB033CA40' -- order_id
),
(
    146718.00, -- amount_to_pay
    'ORDER-2025-04-11-7F04EFA2BB60' -- order_id
),
(
    79318.00, -- amount_to_pay
    'ORDER-2025-04-10-3F03EAA47686' -- order_id
),
(
    79318.00, -- amount_to_pay
    'ORDER-2025-04-10-F4A89E2C4A30' -- order_id
),
(
    49100.00, -- amount_to_pay
    'ORDER-2025-04-08-31BD887341FA' -- order_id
);

SELECT * FROM @StatusRef

SAÍDA:

SELECT * FROM @Product

SAÍDA:

SELECT * FROM @SubProduct

SAÍDA:

SELECT * FROM @Orders ORDER BY dt_stamp

SAÍDA:

SELECT * FROM @Payment

SAÍDA:

SELECT * FROM @SubProductVarKeyValue

SAÍDA:

SELECT * FROM @SubProductVarKeyNames

SAÍDA:

Aqui está o exemplo modificado do script que é semelhante ao primeiro fornecido:

DECLARE @user_id VARCHAR(50) = 'UGUID-2025-01-27-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')

SELECT
    orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
    oa_prod.nested_data AS nested_prod,
    cnb.amount_to_pay
FROM
    @Orders AS orders
RIGHT OUTER JOIN
    @Payment AS cnb
ON
    orders.order_id = cnb.order_id
LEFT OUTER JOIN
    @StatusRef AS stat_ref
ON
    orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
    SELECT
        orders.batch_code,
        orders.order_id,
        prod.prod_name,
        pv_kn.key_name,
        pv_kv.value,
        CASE WHEN
            orders.prod_id IS NOT NULL
        THEN
            prod.disc_price
        WHEN
            orders.prod_var_id IS NOT NULL
        THEN
            prod_var.disc_price
        END AS disc_price,
        orders.quantity
    FROM
        @Product AS prod
    RIGHT OUTER JOIN
        @SubProduct AS prod_var
    ON
        prod.prod_id = prod_var.prod_id
    LEFT OUTER JOIN
        @SubProductVarKeyValue AS pv_kv
    ON
        prod_var.prod_var_id = pv_kv.prod_var_id
    LEFT OUTER JOIN
        @SubProductVarKeyNames AS pv_kn
    ON
        pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
    WHERE       
        prod.prod_id = orders.prod_id
        OR prod_var.prod_var_id = orders.prod_var_id
    FOR JSON PATH,
    INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
    orders.disable = 0
    AND cnb.disable = 0
    AND orders.user_id = @user_id
    AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
    orders.dt_stamp DESC
    --orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

Infelizmente, quando embelezo a saída JSON, isso é o que ele gera:

[
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-11-B71D0E2F5D8C",
        "prod_name": "iPhone",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 45809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 45809
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-D95EB033CA40",
        "prod_name": "Samsung",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 40209,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-7F04EFA2BB60",
        "prod_name": "Nokia",
        "key_name": null,
        "value": null,
        "disc_price": 33150,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-3F03EAA47686",
        "prod_name": "Google",
        "key_name": null,
        "value": null,
        "disc_price": 53509,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-F4A89E2C4A30",
        "prod_name": "Sony",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 25809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-08-31BD887341FA",
        "prod_name": "Lenovo",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 49100,
        "quantity": 1
      }
    ],
    "amount_to_pay": 49100
  }
]

No entanto, preciso que a saída seja algo como isto:

[
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-11-B71D0E2F5D8C",
        "prod_name": "iPhone",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 45809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 45809
  },
  {
    "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-D95EB033CA40",
        "prod_name": "Samsung",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 40209,
        "quantity": 2
      },
      {
        "batch_code": "BGUID-2025-04-11-6D81B58FAE94",
        "order_id": "ORDER-2025-04-11-7F04EFA2BB60",
        "prod_name": "Nokia",
        "key_name": null,
        "value": null,
        "disc_price": 33150,
        "quantity": 2
      }
    ],
    "amount_to_pay": 146718
  },
  {
    "batch_code": "BGUID-2025-04-10-20239FD2059F",
    "nested_prod": [
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-3F03EAA47686",
        "prod_name": "Google",
        "key_name": null,
        "value": null,
        "disc_price": 53509,
        "quantity": 1
      },
      {
        "batch_code": "BGUID-2025-04-10-20239FD2059F",
        "order_id": "ORDER-2025-04-10-F4A89E2C4A30",
        "prod_name": "Sony",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 25809,
        "quantity": 1
      }
    ],
    "amount_to_pay": 79318
  },
  {
    "batch_code": null,
    "nested_prod": [
      {
        "batch_code": null,
        "order_id": "ORDER-2025-04-08-31BD887341FA",
        "prod_name": "Lenovo",
        "key_name": "Condition",
        "value": "new",
        "disc_price": 49100,
        "quantity": 1
      }
    ],
    "amount_to_pay": 49100
  }
]

Observação: O amount_to_payfoi pré-calculado no aplicativo cliente. Portanto, ele deve se tornar uma instância única quando batch_codes duplicados forem agrupados.

Os pedidos do usuário podem causar variações na saída JSON desejada mostrada acima.

Alguém está familiarizado com o problema que estou enfrentando?

Na medida do possível, prefiro não implementar a abordagem Common Table Expression (CTE).

Em primeiro lugar, como estou usando SET XACT_ABORT ON. Se ocorrer uma exceção em qualquer uma das instruções " INSERTou UPDATE" dentro do bloco de transação, ele deve encerrar e reverter toda a transação . Ou seja, se houver um erro em qualquer uma das instruções " INSERTe UPDATE" dentro do BEGIN TRAN. Então ele deve fazer o ROLLBACK da transação automaticamente .

Além disso, estou usando o SQL Server 2017. No meu script, inicializei uma variável de tabela DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50));e ela está temporariamente implementada no meu script para depurar meu procedimento armazenado devido à falta de recursos de depuração integrados no meu IDE do SQL Server Management Studio.

Aqui está meu script T-SQL completo:

SET XACT_ABORT ON; -- To automatically rollback the entire transaction if an exception is encountered
                    
BEGIN TRAN;

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

SELECT TOP 1
    @d_pay_method_id = pay_method_id
FROM
    [testDB].[dbo].[Payment]
WHERE
    method_name LIKE '%Bank Transfer%';
            
SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'PENDING';

SET @d_batch_code = 'BGUID' + '-' + CONVERT(VARCHAR,GETDATE(),23)
    + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13);--DATETIME2(4)-&-12-RANDOM-CODE

INSERT INTO -- first batch
    [testDB].[dbo].[Orders]
(
    order_id,
    batch_code,
    quantity,
    cart_id,
    user_id,
    pay_method_id,
    stat_ref_id
)
OUTPUT
    INSERTED.order_id,
    INSERTED.cart_id
INTO
    @order_id_tbl
SELECT
    'ORDER' + '-' + CONVERT(VARCHAR,GETDATE(),23)
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13) AS order_id,--DATE-&-12-RANDOM-CODE
    @d_batch_code AS batch_code,
    cart.quantity,
    cart.cart_id,
    @user_id AS user_id,
    @d_pay_method_id AS pay_method_id,
    @d_stat_ref_id AS stat_ref_id
FROM
    [testDB].[dbo].[PinkBasket] AS cart
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    cart.stat_ref_id = stat_ref.stat_ref_id
WHERE
    cart.user_id = @user_id
    AND cart.disable = 0
    AND cart.is_selected = 1
    AND cart.is_purchased = 0
    AND cart.is_wishlisted = 0
    AND stat_ref.com_usd_wrds = 'PENDING';

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('first set');

END

INSERT INTO -- first batch
    [testDB].[dbo].[Payment]
(
    cnb_pay_id,
    pay_ref_no,
    amount_to_pay,
    order_id
)
SELECT
    'PGUID' + '-' + REPLACE(REPLACE(REPLACE(CAST(sysdatetime() AS DATETIME2(4)), ' ', '-'), ':', '-'), '.', '-')
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')),1,13), --DATETIME2(4)-&-12-RANDOM-CODE
    @pay_ref_no,
    @amount_to_pay,
    tempData.order_id
FROM
    @order_id_tbl AS tempData;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('second set');

END

SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'ORDERED';

UPDATE -- first batch
    [testDB].[dbo].[PinkBasket]
SET
    stat_ref_id = @d_stat_ref_id,
    is_purchased = 1
WHERE
    cart_id IN
        (
            SELECT
                tempData.cart_id
            FROM
                @order_id_tbl AS tempData
        );

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('third set');

END

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

IF (@place_order_fbatch_cntr + @place_order_sbatch_cntr) >= 4 --@place_order_fbatch_cntr should be 3, and @place_order_sbatch_cntr should be greater than or equal to 1

BEGIN

    COMMIT TRAN;

    SELECT @successful AS json_data;

END

ELSE

BEGIN

    ROLLBACK TRAN;

    SELECT(SELECT exec_checker FROM @cust_debugger FOR JSON PATH, INCLUDE_NULL_VALUES) AS json_data; -- debugging purposes only

END

Cada vez que executei meu procedimento armazenado, recebi esta saída (no meu serviço de API Express.js):

spOutput = "[{\"exec_checker\":\"first set\"},{\"exec_checker\":\"second set\"},{\"exec_checker\":\"third set\"}]"

Você notará que a variável de tabela @cust_debugger obteve com sucesso as três primeiras seções de IF @@ROWCOUNT > 0validação (ou seja, com uma mistura de duas INSERTe uma UPDATEinstrução).

O problema é: por que as duas últimas seções não são executadas corretamente? Mas, como declarei SET XACT_ABORT ONantes BEGIN TRAN, não há exceção durante o processo de transação.

Entretanto, se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON(para fins de teste e investigação), aqui está o seguinte script:

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

SELECT * FROM @cust_debugger

A seguinte saída está sendo gerada:

Minha pergunta é: por que o quarto e o quinto conjuntos funcionam corretamente somente quando executados sem BEGIN TRAN, COMMIT TRAN, e ROLLBACK TRAN?

Se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON), então ele deverá executar a IF @@ROWCOUNT > 0instrução. (ou seja, a variável também deverá ser incrementada: SET @place_order_sbatch_cntr += 1;).

Por outro lado, descobri que as tabelas Producte SubProducttêm um TRIGGER definido, aqui estão as seguintes funções TRIGGER:

GATILHO do produto:

ALTER TRIGGER [dbo].[ProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[Product]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.ProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_id = d.prod_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.ProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.Product
        WHERE
            prod_id IN (SELECT prod_id FROM deleted);
    END
END

GATILHO do subproduto:

ALTER TRIGGER [dbo].[SubProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[SubProduct]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.SubProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_var_id = d.prod_var_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.SubProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.SubProduct
        WHERE
            prod_var_id IN (SELECT prod_var_id FROM deleted);
    END
END

Ainda assim, é questionável por que,

se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRAN e SET XACT_ABORT ON)

Apesar de desabilitar os dois TRIGGERs.

e após novos testes, o problema permanece.

Aqui está o que tenho pensado para resolver meu caso:

• É recomendável separar o processo de leitura complexa (SELECT) para minhas duas últimas seções? Ou seja, preciso passar os dados do SELECT para uma tabela temporária ou variável de tabela. Então, esses dados armazenados serão eventualmente processados ​​com o UPDATE com menor complexidade devido ao conceito de separação de responsabilidades.

• I want to know how to review the processing of my script where the issue arises. Such as if I could review why the UPDATE process doesn't update, then I could also decide what measures of response I needed to take.

• How to check for Locks / Deadlocks / Blocking, hoping that it might cause the unexpected outcomes.

Here are my assumptions that might be causing the conflicts:

• For the Product and SubProduct tables, there are triggers in each of the two tables. But it is defined with INSTEAD OF DELETE. Could it affect the process? Ahhh, because UPDATE statements are also deleting a record, right? The first thing it does is DELETE, and the second is INSERT. Because that's how UPDATE works under the hood of SQL Server, right? I forgot to look at it; it seems it was because of the TRIGGER.

• According to what I've observed. When you create a TRIGGER. You can use the INSERTED table and the DELETED table (if it's handling an UPDATE statement). When you UPDATE a table, it will delete the record first, then it will be stored in the DELETED table, then the DELETED table will pass the data into the INSERTED table and use the argument that is passed to the UPDATE statement instead. Take note, I'm not sure how SQL Server processes the DELETED table and INSERTED table when the UPDATE triggers.

• Since I am using a test server. Here's the screenshot of the test server specifications:

• Is 8GB RAM and 178 GB of storage insufficient for testing purposes?

Does anyone know why this is happening?