1

First things first, since I am using SET XACT_ABORT ON. If an exception happens in any of the INSERT or UPDATE statements within the transaction block, then it should terminate and roll back the entire transaction. Meaning, if there's an error in any of the INSERT and UPDATE statements inside the BEGIN TRAN. Then it should ROLLBACK the transaction automatically.

Moreover, I am using SQL Server 2017. In my script, I've initialized a table variable DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); and it is temporarily implemented in my script to debug my stored procedure due to a lack of built-in debugging features in my SQL Server Management Studio IDE.

Here's my full T-SQL script:

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

Each time I ran my stored procedure, I received this output (in my Express.js API service):

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

You'll notice that the table variable @cust_debugger successfully obtained the first three sections of IF @@ROWCOUNT > 0 validation (i.e., with a mixture of two INSERT and one UPDATE statements).

The problem is, why don't the last two sections execute properly? But, since I declared SET XACT_ABORT ON before BEGIN TRAN, meaning no exception arises during the transaction process.

However, if I extract the portion of the fourth and fifth sets and exclude the instances of BEGIN TRAN and SET XACT_ABORT ON (for testing and investigation purposes), here's the following 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

The following output is being generated:

exec_checker
fourth set
fifth set

My question is, why do the fourth and fifth sets function correctly only when executed without BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN?

If those last two sets are working properly (if I extract the portion of the fourth and fifth sets and exclude the instances of BEGIN TRAN and SET XACT_ABORT ON), then it should execute the IF @@ROWCOUNT > 0 statement. (i.e., the variable should also be incremented: SET @place_order_sbatch_cntr += 1;).

On the other hand, I found out that the Product and SubProduct tables have a TRIGGER defined, here are the following TRIGGER functions:

Product TRIGGER:

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

SubProduct TRIGGER:

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

Still, it is questionable why,

if those last two sets are working properly (if I extract the portion of the fourth and fifth sets and exclude the instances of BEGIN TRAN and SET XACT_ABORT ON)

Despite disabling the two TRIGGERs.

disabled_trigger

and retesting, the issue remains.

Here are the following what I've been thinking about to solve my case:

  • Is it recommended to separate the complex read (SELECT) process for my last two sections? I mean, I need to pass the SELECT data into a temporary table or table variable. Then that stored data will eventually be processed with the UPDATE with lesser complexity because of the separation of concerns concept.

  • 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:

test_server

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

Does anyone know why this is happening?

asked Apr 7 at 9:49
11
  • I think you need to rewrite your question. It's hard to understand what is your actual problem. That said my first guess would be you connected to different database and got different results or some other transaction changed the data between first and second run. Commented Apr 7 at 10:21
  • 1
    Clearly the earlier 3 sections are affecting the last 2, presumable the UPDATE on PinkBasket, and you don't see that when you run it individually. Sample data and expected results for all tables would help immensely. Commented Apr 7 at 10:56
  • Good point @Charlieface. Let me think on how can I provide it. Commented Apr 7 at 11:39
  • @rois, those five sets are grouped in a single transaction. However, I'll review my question again later. Commented Apr 7 at 11:41
  • 1
    Well without sample data for all relevant tables how could we possibly investigate? Maybe construct a dbfiddle.uk with all the relevant tables and a few sample rows for each, showing the issue. The trigger is not relevant, as it's INSTEAD OF DELETE and you aren't deleting. They should be removed anyway, and instead use normal foreign keys. Commented Apr 8 at 9:58

1 Answer 1

1

Here's my workaround:

BEGIN TRY
 
 BEGIN TRAN @multi_place_order_tn;
 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, -- + '-' AS prod_id, -- to simulate and tamper the data integrity (for testing purposes only)
 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_fbatch_cntr += 1;
 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, --+ 'a', -- to simulate error
 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, -- + '-' AS prod_var_id, -- to simulate and tamper the data integrity (for testing purposes only)
 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_fbatch_cntr += 1;
 END
 IF @place_order_fbatch_cntr >= 1 -- @place_order_fbatch_cntr should be greater than or equal to 1
 BEGIN
 -- SAVE TRANSACTION for demonstration purposes only
 SAVE TRANSACTION @multi_place_order_svp;
 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
 [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_sbatch_cntr += 1;
 END
 INSERT INTO
 [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_sbatch_cntr += 1;
 END
 SELECT TOP 1
 @d_stat_ref_id = stat_ref_id --+ '-' -- to simulate error
 FROM
 [testDB].[dbo].[StatusRef]
 WHERE
 com_usd_wrds = 'ORDERED';
 UPDATE
 [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_sbatch_cntr += 1;
 END
 IF @place_order_sbatch_cntr = 3 -- @place_order_sbatch_cntr should be equal to 3
 BEGIN
 COMMIT TRAN @multi_place_order_tn;
 SELECT @successful AS json_data;
 END
 ELSE
 BEGIN
 IF @@TRANCOUNT > 0
 BEGIN
 
 ROLLBACK TRAN @multi_place_order_tn; -- ROLLBACK including the savepoint, to rollback the savepoint only: ROLLBACK TRAN SavepointName;
 END
 END
 END
 ELSE
 BEGIN
 IF @@TRANCOUNT > 0
 BEGIN
 
 ROLLBACK TRAN @multi_place_order_tn; -- ROLLBACK including the savepoint, to rollback the savepoint only: ROLLBACK TRAN SavepointName;
 END
 END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
 
 ROLLBACK TRAN @multi_place_order_tn; -- ROLLBACK including the savepoint, to rollback the savepoint only: ROLLBACK TRAN SavepointName;
END
 
SELECT @ErrorMessage = ERROR_MESSAGE(); 
SELECT @ErrorSeverity = ERROR_SEVERITY(); 
SELECT @ErrorState = ERROR_STATE();
 
RAISERROR (@ErrorMessage,
 @ErrorSeverity,
 @ErrorState
 ); 
END CATCH

Regarding the originally posted issue, I found out that this T-SQL UPDATE statement...

UPDATE
 [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
 );

Causes to not update the last two sections of the update statement because of this WHERE clause:

WHERE
 -- other attributes instances here
 AND cart.is_purchased = 0

So I decided to switch the position of the first three sections with the last two sections. Fortunately, this resolved my issue.

answered Apr 11 at 3:29

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.