Updated value within transaction

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi SQL Gurus,

In my stored procedure, I am using transaction and also calling other stored procedure. Some pseudo-code

SQL

begin transaction
    Update TableA set colA = 'Updated'
    exec SomeotherStoredProc
    if @@Error=0
        Commit Transaction
    else
        RollbackTransaction

In Other stored procedure, I am checking if TableA.ColA has value 'Updated' or not. But I am not getting the value as 'Updated'.

I am aware and tried and confirmed that if I am in same stored procedure, I am getting updated value in TableA.ColA.

Is this correct behavior? If yes, I would like to read further. Please guide.
If No, What am I missing?
Thanks and regards,
Milind

What I have tried:

Tried debugging and found updated value NOT available in child procedure but is available in same stored procedure.

Posted 5-Sep-16 5:52am

MT_

Updated 5-Sep-16 7:20am

v2

Add a Solution

Comments

Wendelius 5-Sep-16 12:30pm

What is the code for SomeotherStoredProc?

MT_ 5-Sep-16 12:54pm

it is just accessing table A "select colA from TableA" and checking if value is 'Updated' for further processing..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-09-05T07:20:00

If your inside the same transaction then you should see the updated value.

Try the following test

SQL

CREATE TABLE TableA (
   ColA nvarchar(100)
);

INSERT INTO TableA VALUES ('Empty');

CREATE PROCEDURE ProcB AS
DECLARE
   @currentvalue nvarchar(100);
BEGIN
   SELECT @currentvalue = ColA FROM TableA;
   PRINT 'ProcB: ' + @currentvalue + ', trancount: ' + CAST(@@trancount AS nvarchar(10));
END;

CREATE PROCEDURE ProcA AS
DECLARE
   @currentvalue nvarchar(100);
BEGIN
   BEGIN TRANSACTION;
   UPDATE TableA SET colA = 'Updated';
   EXEC ProcB
   ROLLBACK;
   SELECT @currentvalue = ColA FROM TableA;
   PRINT 'ProcA: ' + @currentvalue + ', trancount: ' + CAST(@@trancount AS nvarchar(10));
END;

EXEC ProcA

The result should be

(1 row(s) affected)
ProcB: Updated, trancount: 1
ProcA: Empty, trancount: 0

So based on this the problem lies elsewhere. Check for example:
- Are you really inside the same transaction, for example no linked server involved
- Did the UPDATE statement actually update any rows, is the modification done
- If WHERE clause is involved, do you have the same conditions etc...

Where applicable print the intermediate results and/or conditions to ensure that the code path is executed as expected.