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...
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,
1
)
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
try
{
using (SqlConnection conn = new SqlConnection("SomeConnectionString"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SomeStoredProcedure", conn))
{
cmd.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
}