Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot.
This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about good best practices after you’ve written the first one – things like how to catch errors, how to pass in multiple values, how to debug without the debugger, and more.
If you enjoyed this session, check out SQLBits 2022’s free video library with the other sessions from this year, and for all years.
12 Comments. Leave new
With regards to the stored procedure demo. if you use SET XACT_ABORT ON, do you really need to check for @@TRANCOUNT to do a ROLLBACK? I believe SET XACT_ABORT ON will do that automatically.
Brent, I believe you can turn on xact abort globally by turning it on in the server properties. Then all connections that connect to that server will have it turned on by default. This will work for all applications connecting in. I am not sure if it will affect things like SQL Server Agent, CDC, SB, etc.
Also, I do not know of a way to turn it on database by database.
Watched your stored procedure demo, Revealing that CATCH does not catch all errors. Thanks.
Your link to SQLBits free video library brings up a list each of which has this rider “This video is NOT available to view online”. Do you have a link which has viewable videos?
Thanks, glad you liked it! I understand Bits is working through technical issues on other videos – this post was just about my video. Enjoy!
Just when I think I know everything about a topic with SQL Server you show me that I really don’t. Some very useful insights in your video and always a pleasure to learn from you. Much appreciated.
The alternative to the SSMS debugger, which you demonstrated in your stored proc. demo, was waht I gained the most by way of learning. Thanks!
Great presentation… Love the turkey sandwich comparison. Always enjoy best practices videos for SQL dev work.
Thanks for sharing Brent. And I was really convinced that BEGIN TRAN with TRY CATCH would have done the trick… I was so wrong. Learned something new again, thank you!
Great demonstrations as always, thanks for sharing.
Regarding setting xact_abort globally, I always thought this was something you were able to do with User_Options using sp_configure – bit 15 according to the docs
Thanx for the tip about the Table variables not rolling back. That will be very useful.
When you use OPTION (RECOMPILE) on a query within a sproc, what does that actually do? Does it recompile the entire sproc before it’s executed? Does it only recompile that query? If the query was in a loop, would it recompile the query on each loop iteration?
You’re welcome, glad you enjoyed it!
That’s a great question, but it’s outside of the scope of this free training. You can learn more about it in my Mastering Parameter Sniffing class, for example.
Hello Brent.
I really liked this video.
When do you plan to publish the entire course?