wilko100
asked on
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?
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
>>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.
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.
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 CATCHASKER
Great, so to clarify, my script is ok and will fail all inserts even if one fails for whatever reason?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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