SQL Transaction Rollback issues

Question

4.00/5 (1 vote)

See more:

I'm having a bit of trouble trying to get the logic of why my Rollback wont work.

SQL

BEGIN TRAN

UPDATE CustomerBankInfo
SET [Money] = ([Money] - @orderValue)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
FROM Customer
WHERE FirstName = @custFName
AND LastName =@custLName) AND [Money] > @orderValue;

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 10
END

The thing is, even if orderValue is less than Money the rest of the procedure still runs. Am I totally missing the point? I feel like I am, can someone help please!

--- New Issue

SQL

BEGIN TRAN

SELECT @currentBalance = [Money] 
FROM CustomerBankInfo 
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
FROM Customer
WHERE FirstName = @custFName
AND LastName =@custLName)
 
IF ((@currentBalance - @orderValue) < 0)
BEGIN
ROLLBACK TRAN 
RAISERROR ('Insuffienct funds available for customer',
               16, -- Severity.
               1 -- State.
               )
END

UPDATE CustomerBankInfo
SET [Money] = ([Money] - @orderValue)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
FROM Customer
WHERE FirstName = @custFName
AND LastName =@custLName)

Now it does nothing and just comes up with an unhanded exception. In my head the logic is right maybe im seeing past something though. Any help would be much appreciated.

Posted 15-Dec-11 4:10am

WurmInfinity

Updated 15-Dec-11 7:01am

v4

Add a Solution

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)

Dylan Morley · Accepted Answer · 2011-12-15T04:45:00

You're only rolling back your transaction if an error has occurred and the @@ERROR variable is set,

Your SQL statement will run OK, it won't generate an error (unless you had some sort of logic built in to table triggers or along those lines!)

What you are talking about is business logic, which is completely different to an SQL Error. You need to define your logic and raise your own error in these cases...

SQL

SELECT @currentBalance = [Money] FROM CustomerBankInfo 
WHERE FirstName = @custFName
AND LastName =@custLName

IF ((@currentBalance - @orderValue) < 0)
BEGIN
    ROLLBACK TRAN 
    RAISERROR ('Insuffienct funds available for customer'
               16, -- Severity.
               1 -- State.
               )
END

--Edit - additional info

If you calling this from c#, you need to handle the exception there. Flow is like this...

1) You call the SQL procedure from c#, passing in parameters
2) SQL procedure runs and encounters a business logic error (insufficent funds). Error is raised
3) This results in an SQLException, which your c# needs to handle
4) In your c# catch block, look at the exception object and extract error messages
5) Display error message to the user

e.g

C#

try
{
    using (SqlConnection conn = new SqlConnection("SomeConnectionString"))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SomeStoredProcedure", conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}
catch (SqlException ex)
{
    // Your exception details here. Use your debugger, look at the exception object. Determine how to display exception details to the client
}