April 28, 2011 at 11:51 am
We currently have a t-sql code template we use to process all single table DML updates that uses the new as of SQL 2005 "TRY-CATCH" syntax/method. I was curious if any of the more versed SQL gurus would glance over this and provide feedback on it, noting if there are any recomended changes they'd make to this template.
Thanks
/*T-SQL COde template for single table DML UPdates*/
BEGIN TRANSACTION;
BEGIN TRY
/*Place DML Statements here.*/
/*Single table/row update example*/
UPDATE dbo.MYTABLE
SET myColumn01 = 'SomeValue'
WHERE PrimaryKeyColumn = 100
/*Single table/multiple row update example*/
UPDATE dbo.MYTABLE
SET myColumn02 = 0
WHERE PrimaryKeyColumn IN(100,101,13)
END TRY
/*After executing the above DML stamenets we now run a check via the CATCH block using the ERROR_XXXX built in error functions*/
BEGIN CATCH
SELECT ERROR_NUMBER() AS 'ErrorNumber',
ERROR_SEVERITY() AS 'ErrorSeverity',
ERROR_STATE() AS 'ErrorState',
ERROR_PROCEDURE() AS 'ErrorProcedure',
ERROR_LINE() AS 'ErrorLine',
ERROR_MESSAGE() AS 'ErrorMessage';
/*If @@TRANCOUNT is greater then zero then the above select on the ERROR functions return a row listing error info. */
/* If an error is encountered then we need to rollback the changes made by the DML statements in the TRY block.*/
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
/*If the CATCH block returned no rows then there were no errors so we now commit our changes.*/
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
Kindest Regards,
Just say No to Facebook!June 2, 2011 at 10:02 am
I would move begin tran just after begin try, and commit tran just before end try.
That's because not all transactions can be commited.
If commit raises error and it is WITHIN try-catch, transaction will be properly closed by rollback in the catch block.
June 2, 2011 at 11:28 am
Vedran Kesegic (6/2/2011)
I would move begin tran just after begin try, and commit tran just before end try.That's because not all transactions can be commited.
If commit raises error and it is WITHIN try-catch, transaction will be properly closed by rollback in the catch block.
Thanks
BTW - I briefly took a look at your SQLXDetails stuff and I had a question. How were you able to plug into SSMS? From every place I looked the ability to do a SSMS Add_in like you can do for the Office products is at best a hack.
Thanks
Kindest Regards,
Just say No to Facebook!June 2, 2011 at 1:39 pm
It's very difficult because there is almost no documentation on that. Documentation for MS Visual studio plugins has some mutual interfaces that can help. There are also some rare articles on the net, and if you are lucky some good people on the net may help (like Mladen Prajdic that was kind to help at the beginning). Prepare with good nervs for many many hours (months) of experimenting 🙂
Definitely not an easy stuff.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply