The Art Of The SQL Server Stored Procedure: What You Need To Know

Posted on by Erik Darling

For Your Eyes Only

I’ve been keeping a mental list of things of things I often have to adjust or fix in stored procedures I work on.

Some of them are a touch pedantic, but the majority of them are lessons and practices I’ve learned (often the hard way) over the years while working on things:

  • Performance tuning
  • Building processes
  • Fixing issues and errors

The thing is, I see the same mistakes over and over again. You probably do too, but you may not know they’re mistakes.

Judging by the code I see, most developers out there don’t.

No offense, it’s just… if all you do is begin a transaction and then try to commit it at the end, with no thought given to error handling or rolling back, what is the point of you?

If you do this, go ahead and tell me why in the comments. I’d love to know.

Anyway.

Table Of Contents

Here’s what I’ll be talking about over the next month or so.

  1.  ANSI/SET options
  2.  Commenting
  3.  Formatting
  4.  Error Handling
  5.  Debugging
  6.  Batching Modifications
  7.  Transactions
  8.  Isolation Levels
  9.  Locking Hints
  10.  Parameter data types
  11.  Table valued parameters/Passing lists
  12.  Conditional logic
  13.  Local variables
  14.  Wrapper stored procedures
  15.  Dynamic sql
  16.  Temporary objects
  17.  Parameter sniffing
  18.  Dynamic searches
  19.  Pagination
  20.  Cursors and Loops

The list might change a little, but it’s the basic outline of what I care to talk about.

Some posts might be shorter than others, because there’s not a lot to say about certain things beyond “this is right, and this is why”.

Hopefully these lessons start to sink in.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts

2 thoughts on “The Art Of The SQL Server Stored Procedure: What You Need To Know”

  1. I’ve done that, until I read https://sommarskog.se/error_handling/Part1.html

    If you can improve that text, I’ll be most impressed.
    Except one thing, where I do not follow Erland.
    I write Rollback, not Rollback Transaction.
    Then I do not have to write “;throw”, unless I want to signal that I’ve read somebody Swedish’s homepage.

Comments are closed.