October 10, 2012 at 6:32 am
Thanks for the question.
October 10, 2012 at 7:00 am
Nice question.
Almost too easy.
Tom
October 10, 2012 at 8:33 am
Nice and Easy Question of the Day +1
October 10, 2012 at 11:40 am
For some reason I expected the 10/0.00001 to fail, expecting SQL to convert the 0.00001 to an integer with the integer 10 being on the left of the equation. I was mistaken.
Tony
------------------------------------
Are you suggesting coconuts migrate?
October 10, 2012 at 11:44 am
Hugo Kornelis (10/10/2012)
A.jafari (10/9/2012)
Please illustrate diffrent between "Begin Catch" And "Try Except"You can read about TRY ... CATCH here: http://msdn.microsoft.com/en-us/library/ms175976.aspx
In T-SQL, "Try Except" is invalid syntax. I have no idea what language this comes from and what it does, but it sounds as just a slightly different way to solve the same problem (error handling).
It's Borland Delphi (88.5% sure).
October 10, 2012 at 1:42 pm
And... what was the challenge?
😀
October 10, 2012 at 5:32 pm
Thanks for the question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 10, 2012 at 8:31 pm
Nice question. I was looking for a catch (no pun intended) but it was just a good question on the basics. Thanks!
October 10, 2012 at 9:57 pm
Thanks For Answer, sorry "Try Except" is wrong ,it is Commad Of Delphi ,
,i Want to say Diffrent Bettween "Begin Tran" And "Begin Catch"
Because i always Use Transaction with This command
--------------------------------------
Begin Tran
sql_statement ...............
IF @@error <> 0
rollback tran
else
commit tran
--------------------------------------------------
Is it ok ??
October 11, 2012 at 4:03 am
A.jafari (10/10/2012)
Thanks For Answer, sorry "Try Except" is wrong ,it is Commad Of Delphi ,,i Want to say Diffrent Bettween "Begin Tran" And "Begin Catch"
BEGIN TRAN .... ROLLBACK TAN or COMMIT TRAN is a completely different concept than BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH.
Transactions (that's what "TRAN" is short for) are primarily used to ensure that a series of statements that form a single unit of work are either completely executed, or not at all. The classic example is the bank account - if I pay you 500 dollars, that amount is subtracted from my account and added to your account. Assuming that this is implemented using two update statements, we both prefer that either both statements succeed, or both statements fail. (Okay, I guess I may have a slight preference for both failing, whereas both succeeding would be your first choice;-)). Neither of us want the statement that takes money from my account to succeed and the statement that adds it to yours to fail, as that would make the 500 dollars disappear completely. This can be ensured by starting a transaction first, then checking for errors, and either forcing a rollback of the entire transaction (if anything went wrong), or committing the entire transaction (is all went okay).
TRY ... CATCH is a method for handling errors in your code. SQL Server didn't have this in versions prior to SQL Server 2005; the only way to detect errors was to check for the value of @@ERROR directly after each statement - and even that was flaky, since some errors would abort the batch, causing the next statement not to run! TRY ... CATCH is a huge improvement, though still far from perfect. You can put as many statements as you want in the TRY block, and errors will cause the CATCH block to be executed (though there are still some errors that misbehave!)
In practice, transactions and try/catch are often used in combination. For the banking example, the pseudo-code of the logic would most probably be like this:
BEGIN TRANSACTION;
BEGIN TRY;
-- First update, to take 500 dollars from my account
UPDATE (....)
-- Second update, to add 500 dollars to your account
UPDATE (...)
-- Execution only comes here if no errors, so safe to commit now
COMMIT TRANSACTION;
END TRY
BEGIN CATCH;
-- Something went wrong inside the catch block; roll back transaction
ROLLBACK TRANSACTION;
-- Probably more error handling needed, such as logging the error, making
-- sure an error message is sent back to the calling client program, etc.
END CATCH;
Because i always Use Transaction with This command
--------------------------------------
Begin Tran
sql_statement ...............
IF @@error <> 0
rollback tran
else
commit tran
--------------------------------------------------
Is it ok ??
It's not wrong, but it is outdated (pre-SQL Server 2005). TRY CATCH gives better control and cleaner code.
For more information, see http://www.sommarskog.se/error_handling_2005.html
October 11, 2012 at 4:10 am
Hugo Kornelis (10/11/2012)
Transactions (that's what "TRAN" is short for) are primarily used to ensure that a series of statements that form a single unit of work are either completely executed, or not at all. The classic example is the bank account - if I pay you 500 dollars, that amount is subtracted from my account and added to your account. Assuming that this is implemented using two update statements, we both prefer that either both statements succeed, or both statements fail. (Okay, I guess I may have a slight preference for both failing, whereas both succeeding would be your first choice;-)). Neither of us want the statement that takes money from my account to succeed and the statement that adds it to yours to fail, as that would make the 500 dollars disappear completely. This can be ensured by starting a transaction first, then checking for errors, and either forcing a rollback of the entire transaction (if anything went wrong), or committing the entire transaction (is all went okay).
If the update that subtracts from your account fail and the later will succeed, you both will be happy. :w00t:
October 11, 2012 at 4:42 am
Hugo Kornelis (10/11/2012)
BEGIN TRAN .... ROLLBACK TAN or COMMIT TRAN is a completely different concept than BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH.
Very very thanks for your complete answer.:w00t:
October 11, 2012 at 5:32 am
Nice question. Thanks.
October 11, 2012 at 9:32 am
honza.mf (10/11/2012)
Hugo Kornelis (10/11/2012)
Transactions (that's what "TRAN" is short for) are primarily used to ensure that a series of statements that form a single unit of work are either completely executed, or not at all. The classic example is the bank account - if I pay you 500 dollars, that amount is subtracted from my account and added to your account. Assuming that this is implemented using two update statements, we both prefer that either both statements succeed, or both statements fail. (Okay, I guess I may have a slight preference for both failing, whereas both succeeding would be your first choice;-)). Neither of us want the statement that takes money from my account to succeed and the statement that adds it to yours to fail, as that would make the 500 dollars disappear completely. This can be ensured by starting a transaction first, then checking for errors, and either forcing a rollback of the entire transaction (if anything went wrong), or committing the entire transaction (is all went okay).
If the update that subtracts from your account fail and the later will succeed, you both will be happy. :w00t:
True... but the other party in this transaction (the bank) won't be.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply