This Blog Has Been Moved !

This Blog Has been moved to http://aleemkhan.wordpress.com

Error Handling has always been cumbersome in SQL server, with all the unstructured @@Error checks scattered all around the place, this makes the T-SQL code harder to read and many errors go unaddressed due to simple coding mistakes. SQL Server 2005 comes to the rescue with the new TRY…CATCH block, but it is important how the try catch block is used. Also, recently I have had many issues with the Nested Transactions and the Nested Stored Procedures each using its own transaction.

The issues I am referring to are the things like the TRANCOUNT check on the entry and exit of each stored procedure. For example you are in a stored procedure which is called by another outer procedure (both using BEGIN TRAN), when you call Rollback in the second procedure, SQL Server will throw the following error on the exit of the second procedure and will leave the transaction is a unconsistent state

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count =0. "

Similarly, as I mentioned in a previous post a single ROLLBACK will set the @@TRANCOUNT to zero and any subsequent ROLLBACK in an outer procedure will give the following error

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

Infact, all these small things are very trivial in T-SQL and there are several ways to solve them, for example some people try not to use the transactions at database at all, they take the TRANSACTION Management completely to their data layer and manage it through MTS. (This was the case in one of the projects I worked on last year). Similarly, some people would always take care that Transaction is being used in only the outer procedure which is to be called by the data layer etc. However, this can easily break if you are not in control of all the T-SQL code you are writing,like if you have an encrypted third-party stored procedure, you wouldn’t know if it calls the ROLLBACK/COMMIT which may break your logic in the outer procedure.

There should be some standard pattern for the Error Handling and writing standard stored procedure, and especially with the availability of the new Error Handling features in SQL Server 2005. So, I came up with the following Pattern for a standard stored procedure using an independent transaction. This stored procedure will not break on the behavior of the procedure it calls and is returning the error messages to its caller in a standard way (RAISERROR)

ALTER PROCEDURE [dbo].[StandardTSQLProcedure]
AS

BEGIN TRY

DECLARE
@TranStarted bit

SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )

BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
-------------------------------------------------------
--Your Standard T-SQL Code for the Procedure Comes Here



-------------------------------------------------------


IF( @TranStarted = 1 )

BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END

RETURN(0)


END TRY

BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER();
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH

Comments

45 comments have been posted.
Posted @ 5:54 PM
yeah T-SQL kya hai ... yea T-SQL kya..
 


Anonymous Anonymous
Posted @ 6:38 PM
I transferred albums with no artwork to my iphone, so a few albums in my iphone do not have Artwork covers, I did not see the feature "get album artwork" under the advanced tab in itunes until after i had transferred the albums from my library to the iphone. Now that itunes found artwork for my albums in my library, how can I transfer the Artwork to the albums on my iphone?
[url=http://forexrobot-review.info]best forex software[/url] [url=http://www.mfbb.net/forumolivierdes/forumolivierdes/forumolivierdes-post-2428.html#2428]unlock iphone[/url]
 

Anonymous Anonymous
Posted @ 11:22 AM
Hey I'd like to thank you for such a terrific made forum!
I was sure this is a nice way to introduce myself!

Sincerely,
Edwyn Sammy
if you're ever bored check out my site!
[url=http://www.partyopedia.com/articles/hawaiian-party-supplies.html]hawaiian Party Supplies[/url].
 

Anonymous Anonymous
Posted @ 4:23 PM
I just discovered the website who writes about
Several
home based business reviews

If you want to know more here it is
home based business
www.home-businessreviews.com
 

Anonymous Anonymous
Posted @ 3:12 AM
hello people. I'm honestly into shoes and I had been digging allowing for regarding that meticulous brand. The prices due to the fact that the shoes are approximately 300 bucks on every site. But finally I base this area selling them for the benefit of half price. I in reality like these [url=http://www.shoesempire.com]prada sneakers[/url]. I will definetly purchase them. what can you tell me about these?
 

Anonymous Anonymous
Posted @ 3:25 PM
Artistically done is richer reconsider than extravagantly said.
 

Anonymous Anonymous
Posted @ 6:58 PM
Artistically done is better than spectacularly said.
 

Anonymous Anonymous
Posted @ 10:41 PM
Splendidly done is richer reconsider than well said.
 

Anonymous Anonymous
Posted @ 2:32 AM
Splendidly done is sick than extravagantly said.
 

Anonymous Anonymous
Posted @ 6:32 AM
Lovingly done is better than extravagantly said.
 

Anonymous Anonymous
Posted @ 10:38 AM
Splendidly done is richer reconsider than spectacularly said.
 

Anonymous Anonymous
Posted @ 2:53 PM
Splendidly done is well-advised b wealthier than extravagantly said.
 

Anonymous Anonymous
Posted @ 11:35 AM
A the huan race who dares to waste bromide hour of age has not discovered the value of life.

[url=http://www.linksheaven.com/forums/index.php?showuser=21308]Mark[/url]


Marry
 

Anonymous Anonymous
Posted @ 10:33 AM
We should be painstaking and fussy in all the intelligence we give. We should be especially prudent in giving advice that we would not about of following ourselves. Most of all, we ought to evade giving counsel which we don't follow when it damages those who take us at our word.

bench dog

[url=http://bench-dog-66.webs.com/apps/blog/]bench dog[/url]
 

Anonymous Anonymous
Posted @ 12:45 AM
A humankind begins scathing his discernment teeth the initially chance he bites eccentric more than he can chew.
 

Anonymous Anonymous
Posted @ 1:35 PM
To be a noble charitable being is to have a philanthropic of openness to the far-out, an skill to trusteeship undeterminable things beyond your own control, that can take you to be shattered in very exceptionally circumstances on which you were not to blame. That says something uncommonly impressive with the fettle of the honest life: that it is based on a trust in the up in the air and on a willingness to be exposed; it's based on being more like a plant than like a sparkler, something somewhat tenuous, but whose mere precise attractiveness is inseparable from that fragility.
 

Anonymous Anonymous
Posted @ 1:56 PM
Vex ferments the humors, casts them into their adapted channels, throws substandard redundancies, and helps nature in those secretive distributions, without which the body cannot subsist in its vigor, nor the soul role of with cheerfulness.
 

Anonymous Anonymous
Posted @ 10:55 PM
can you buy xanax online buy xanax safely online - xanax online no rx
 

Anonymous Anonymous
Posted @ 11:11 PM
Hi, guantanamera121212
 

Anonymous Anonymous
Posted @ 10:08 AM
Over living a healthier lifestyle and losing consequence at the changeless time. These supplements are backed up nigh several researches and are manufactured bearing in mind the highest calibre standards in system to optimize the nutritional requirements of the somebody body. Healthiness supplements are custom made pills, powders or syrups which put up unused sustenance or medicinal benefits. Haleness supplements bear an unending laundry list of applications. Enteric coated tablets are made to be protected next to your thirst acid, because uncountable specialty nutrients are destroyed by the acid in your stomach, so enteric coating is required.




7qwpf434 2013 hgh energizer lowest price 1biby300 2013 hgh energizer working 7ibud299 2012 hghenergizerreviews2read.webs.com 3rruz850 2013 hgh energizer product 6dnia506 2013 http://hghenergizerpills.webs.com 5llmp088 2012 hgh energizer supplement 7kegc922 2012 http://hghenergizergnc.webs.com 5rnor705 2012 hghenergizerscamornot.webs.com 7hykd720 2012 hgh energizer 2bufv169 2012
 

Anonymous Anonymous
Posted @ 11:19 AM
A get-up-and-go on the stretch means that multifarious people today are only getting at the most a certain righteous straightforward meal each and every time, and the rest of the conditions is done up at diversified quick like a bunny grub establishments as this is a technique of extenuating venerable time. We sup too much sugar which means that we advance on mass and over possess problems with our teeth, children who include too much sugar in their diets may also realize the potential of behavioral problems. Healthiness supplements are specially made pills, powders or syrups which present surplus victuals or medical benefits.


provillus in uk 6iduo113 2012 best hair regrowth product 9lpny773 provillus reviews 8pqmi226 buy provillus treatment 4kwud735 isprovillusascam.webs.com 9kdqr256 provillus side effects 5yjaf972 provillus for men 2cmxu777 provillusforwomenexplained.webs.com 6hpqa642 http://provillusingredients.webs.com 9xcxu724 provillus work 8hzgx501 order provillus provillus men 1taiv578 2013
 

Anonymous Anonymous
Posted @ 12:36 PM
I about I puissance start exercising in the morning to get me succeeding and test to after work. A healthy regime choice consist of a decorous balance of minerals, vitamins and other nutrition from the food that we eat. Psychedelic companies are time after time lifesavers. Herbs and natural ingredients had been a part of the sanative way of the Orient in the service of ages. Yes there is ample sign that you should be engaging all above well-being supplements. We're fatter, less trim, and fall heir to cancer way more ordinarily than we acquainted with to a hundred years ago. There are also herbal and natural health supplements. The unartificial treatment options are operative and secure and should not be discounted in maintaining prostate health.


zetaclear clear nail solution 9uvxh879 2013 buy zetaclear online 1gply430 zetaclear all natural 6odfq737 zetaclear 6hmkv287 zetaclear treatment 9gezn742 zetaclearinformation.webs.com 4zfie796 zetaclear official website 0slhh224 cheap zetaclear 2sonh443 zetaclear zetaclear for sale 8lant249 2012
 

Anonymous Anonymous
Posted @ 2:03 PM
Many of us are, for that reason, considering supplementing our bread with normal constitution supplements. The beetroot power has been of dislike in the operation of weaken and tiredness as a inclusive restorative and supports the weight loss programs effectively. Other components of ginkgo biloba contain hard-working antioxidants that storm those damaging unsolicited radicals in our bodies. But in advance that you need to announce this article carefully. To own a strong and inelastic association, gentlemen should accompany a sure and balanced eating clothes system. Also the accepted anti-oxidants up to date in these supplements are said to be very productive against the cost-free radicals that invoice your way and pass you thrive old. Separately from being darned able, these supplements sooner a be wearing minimum side effects and are patently harmless.



wartrol genital warts treatment 1mesr059 2012 buy wartrol product 0vvrc578 buy wartrol 3gypp476 buy wartrol 9jezz538 cheap wartrol online 9khpc403 officialwartrol.webs.com 5jqmr587 thewartrolscam.webs.com 6auua682 http://wartrolwork.webs.com 6czmc684 treatment for genital warts wartrol lotion 1wdbs661 2013
 

Anonymous Anonymous
Posted @ 6:56 PM
Yet, when you be familiar with that there is a change one's mind progress, it makes wisdom to take that footpath instead. Harnessing the beneficial qualities of herbs and natural extracts, these supplements be relevant to in form of pills, capsules, syrups or powders and bid simple, healthy therapy. Everyone specimen is the articulation system familiar in the tablets. With the perpetual studies being conducted nowadays, you can stylish try developing your own daily supplement design while you also take rare supplements individually. Many people palm natural health supplements as a accede of coping with the stress and pressures of existence, while others just accept them because they on that vitality with an increment of honourable health benefits make be the denouement upward of the long term. Other names after this vine are blissful foremothers or Aaron's rod. These days, you can't be too concerned round your health. When a confine has impotency problems, it is most right brought on cardiovascular diseases or Category 2 diabetes.



7ex5op9 raspberry ketones 4lg9kn9 benefits of raspberry ketones and green tea 2ht3di4 raspberry ketones dosage for weight loss 9ig1sg6 l carnitine plus raspberry ketones reviews 8nf7kb6 best all natural raspberry ketones 5 mg 3sq2jm0 raspberry ketones official 7it7yu2 can i buy raspberry ketones at gnc 2mn6ly0 cheaper raspberry ketones 5fe1qn4 red raspberry ketones drops 7lu7qq6
 

Anonymous Anonymous
Posted @ 10:14 PM
[url=http://loveepicentre.com/faq.php][img]http://loveepicentre.com/uploades/photos/11.jpg[/img][/url]
biloxi dating [url=http://loveepicentre.com]best of dating[/url] 1000 free dating sites
randall knife dating [url=http://loveepicentre.com/success_stories.php]disable dating service[/url] christian dating arkansas
senior citizens dating in cincinnati ohio [url=http://loveepicentre.com/]domestic and dating abuse[/url] dating games strip club
 

Anonymous Anonymous
Posted @ 2:23 PM
official raspberry ketones website 8ke5hy8 raspberry ketones 1234http://raspberryketones1234.tumblr.com natural red raspberry ketones 2lv9bh8
 

Posted @ 7:15 PM
The African mango diet is one of the latest crazes on the weight loss supplement front. The diet is based on irvingia gabonensis extract obtained from the African mango fruit. Irvingia extract has been used for centuries as a medicinal substance in Africa, but not until recently has its beneficial effects on weight loss been acknowledged. Since then, several clinical studies have evaluated irvingia gabonensis extract for the treatment of obesity.
african mango plus
 

Posted @ 7:16 PM

Dr. Oz. even conducted his own in-house study which consisted of several overweight women who were given a 400mg capsule of Green Coffee Bean extract each day. After only two weeks, the participants lost an average of two pounds. If you’ve been trying to lose weight and have only lost a few pounds or you’ve reached a weight loss plateau, Green Coffee Bean may be just the extra boost your body needs to burn more fat and in return, you’ll reach your weight loss goal. It’s a safe, natural and proven way to lose weight; but you’ll never know if it works for you until you try it.

green coffee bean extract where to buy
 

Anonymous Anonymous
Posted @ 8:42 AM
yc6xk4 bf3jf8 zetaclear pt1ao2 uy6sv0 http://zetaclearbuy.webs.com/ http://zetaclearreviewsx.webs.com/ http://toenailfungusremedies.webs.com/ http://nailfungustreatmenttoday.webs.com/
 

Anonymous Anonymous
Posted @ 6:11 AM
Hello There. I discovered your blog the usage
of msn. That is a very neatly written article.

I'll make sure to bookmark it and come back to read extra of your useful info. Thank you for the post. I will certainly comeback.

My blog ... buy wartrol
 

Anonymous Anonymous
Posted @ 1:02 AM
Have you ever thought about including a little bit more than just your articles?
I mean, what you say is important and everything. Nevertheless think about if you
added some great photos or videos to give your posts
more, "pop"! Your content is excellent but with pics and clips, this site could certainly be
one of the greatest in its niche. Fantastic blog!

my homepage ... buy wartrol
 

Anonymous Anonymous
Posted @ 12:33 AM
I just couldn't depart your website before suggesting that I extremely loved the standard information an individual provide for your guests? Is going to be back ceaselessly in order to investigate cross-check new posts

My web blog: Personal Site
 

Anonymous Anonymous
Posted @ 9:29 AM
bookmarked!!, I really like your web site!

Also visit my site :: buy phen375
 

Anonymous Anonymous
Posted @ 3:16 PM
Attractive component of content. I simply stumbled upon your site and in
accession capital to say that I acquire in fact enjoyed
account your blog posts. Any way I'll be subscribing in your feeds and even I achievement you access persistently quickly.

Feel free to visit my website: http://phen375reviews-hq.blogspot.com/
 

Anonymous Anonymous
Posted @ 8:18 PM
Great beat ! I would like to apprentice while you amend your website, how can i subscribe for a weblog site?
The account helped me a appropriate deal. I have been tiny bit familiar of this
your broadcast offered bright transparent idea

Check out my webpage ... phen375 reviews
 

Anonymous Anonymous
Posted @ 9:14 PM
Does your site have a contact page? I'm having trouble locating it but, I'd like to send you an email.
I've got some suggestions for your blog you might be interested in hearing. Either way, great site and I look forward to seeing it grow over time.

my site wartrol review
 

Anonymous Anonymous
Posted @ 9:28 PM
Woah! I'm really loving the template/theme of this website. It's simple, yet
effective. A lot of times it's very difficult to get that "perfect balance" between user friendliness and visual appearance. I must say that you've done a fantastic job with this.
Also, the blog loads very fast for me on Firefox. Superb Blog!


Feel free to visit my blog post - buy wartrol
 

Anonymous Anonymous
Posted @ 5:24 AM
Hi there! I know this is kind of off topic but I was wondering if you knew where
I could find a captcha plugin for my comment form?
I'm using the same blog platform as yours and I'm having problems
finding one? Thanks a lot!

Feel free to surf to my blog post - wartrol
 

Anonymous Anonymous
Posted @ 6:37 AM
Marvelous, what a web site it is! This webpage provides useful information to us, keep it up.


Here is my web blog: wartrol scam
 

Anonymous Anonymous
Posted @ 4:03 PM
I like the helpful information you provide for your articles.
I'll bookmark your blog and check again here regularly. I'm quite sure I'll be informed lots of new stuff right here! Good luck for the following!

Here is my homepage; does phen375 work
 

Anonymous Anonymous
Posted @ 1:40 PM
online dating servies http://loveepicentre.com/advice/ expatriate dating
international dating atheist [url=http://loveepicentre.com/map/]best dating service in costa rica[/url] interracial relationships and dating
dating coaches [url=http://loveepicentre.com/articles/]absolute age dating[/url] 18oo numbers for phone dating [url=http://loveepicentre.com/user/AL007/]AL007[/url] kid dating quiz
 

Anonymous Anonymous
Posted @ 3:32 PM
muslims dating site http://loveepicentre.com/success_stories/ vulvadynia dating
speed dating in jacksonville florida [url=http://loveepicentre.com/contact/]free dating services with free messaging[/url] dating housewives
dating agency large women [url=http://loveepicentre.com/testimonials/]dating male co workers[/url] los angeles hiv dating [url=http://loveepicentre.com/user/Rocky3283/]Rocky3283[/url] rageed online dating
 

Anonymous Anonymous
Posted @ 7:48 AM
Please let me know if you're looking for a article writer for your site. You have some really great posts and I believe I would be a good asset. If you ever want to take some of the load off, I'd absolutely love to write
some content for your blog in exchange for a link back to mine.
Please send me an email if interested. Regards!


my site - mftot.jointokyo.org
 

Anonymous Anonymous
Posted @ 11:30 AM
Why viewers still use to read news papers when in this technological globe the whole thing is accessible on net?


My site ... phen375 reviews