SQL Error handling using Try Catch

Hi

This may seem like a simple question but im trying to get my head around error trapping using the TRY CATCH method.
I have created a script that inserts 3 rows into a table and wrapped a TRY Caych around it. I would like the script NOT to insert ANY rows into the table if one row fails to insert, is my script the best way to acheive this or is there a better way, maybe line by line?
USE Database1

BEGIN Try
Begin Tran
INSERT INTO Package (PackageId 
,PackageServerOrder 
,PackageTypeId 
,DateCreated 
,DateInserted) -- Extra column for complete DB to be populated with getdate below. 
SELECT 
PackageId 
,PackageServerOrder 
,PackageTypeId 
,DateCreated 
,GETDATE() 
FROM
Database2.dbo.Package
Where Database2.dbo.Package.PackageId In (
'5760B626-92B2-4791-9780-08DF58DA83C8',
'B7021CD6-DA16-4C2F-A07C-0C3B2343FC3E',
'17090F6C-C117-4894-80EA-10A18A17D94C',
)
Commit Tran
End Try
--If any rows fail do not commit
Begin Catch
Rollback Tran

Error_Message() as ErrorMessage;

End Catch

Open in new window

wilko100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

What you need to do is wrap the inserts with a begin transaction ... commit transaction, and in the catch start with a rollback transaction. If being really fussy do a while @@trancount > 0 rollback trans

HTH
  David
0
Anthony PerkinsCommented:
>>What you need to do is wrap the inserts with a begin transaction ... commit transaction<<
The author already has done that.  Although I find the usefullness of a Transaction on a single SQL statement to be pointless.

>>I would like the script NOT to insert ANY rows into the table if one row fails to insert,<<
That is the way the INSERT (or UPDATE and DELETE for that matter) work.
0
Anthony PerkinsCommented:
I have corrected and formatted for readability the SQL Script posted:
USE Database1

BEGIN TRY
    BEGIN TRAN
    INSERT  INTO Package
            (PackageId,
             PackageServerOrder,
             PackageTypeId,
             DateCreated,
             DateInserted
            ) -- Extra column for complete DB to be populated with getdate below. 
            SELECT  PackageId,
                    PackageServerOrder,
                    PackageTypeId,
                    DateCreated,
                    GETDATE()
            FROM    Database2.dbo.Package
            WHERE   Database2.dbo.Package.PackageId IN (
                    '5760B626-92B2-4791-9780-08DF58DA83C8',
                    'B7021CD6-DA16-4C2F-A07C-0C3B2343FC3E',
                    '17090F6C-C117-4894-80EA-10A18A17D94C'--,
)
    COMMIT TRAN
END TRY
--If any rows fail do not commit
BEGIN CATCH
    ROLLBACK TRAN

-- Error_Message() as ErrorMessage;

END CATCH

Open in new window

0
wilko100Author Commented:
Great, so to clarify, my script is ok and will fail all inserts even if one fails for whatever reason?
0
Anthony PerkinsCommented:
>>Great, so to clarify, my script is ok and will fail all inserts even if one fails for whatever reason? <<
Yes.  And it would still do that if you did not have transactions or TRY ... CATCH.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.