T-SQL Tuesday #024 – Prox ‘n’ Funx (Procedures & Functions)

T-SQL Tuesday is a monthly SQL Server blogging event.  One of us sets a topic for the month’s event, and we all have a week in which to write a blog post that fits that topic.  This month’s event is hosted by Brad Schulz (blog), and he’s chosen the subject of Procedures & Functions.

Should I?

I recently saw a Tweet on the #sqlhelp tag asking “What advantages do Stored Procedures have in MSSQL? Should I learn to use them?”
Original Tweet

My answer – Yes. Hell yes.

Why Should I?

This is harder (for me) to articulate. Some reasons that come to my mind include:

Less network traffic

Consider a busy firm with a case management system. To build the case handlers’ views of what’s what involves a complex set of SQL queries. Now, should these be held at the client side, and sent to the server one at a time, or batched up and sent (and executed) all at once? And isn’t it easier for the programmer / maintainer of the codebase to see a call to exec GetUserCaseLoad @UserID=foo rather than the masses of code?


While we’re on the subject of maintenance, imagine that you’re working in an environment where the code is used in many applications. Would you find it easier to change the stored procedure, or to find all instances of that code in your application base?

Consistency of Operation

This is an important area – if you’ve got many applications accessing the same data, then it can be helpful to ensure that this is always accessed in the same way. This can significantly reduce the risk of deadlocks.

Yeah, but…

Look, I know I don’t make a particularly good argument here in favour of it. There’s loads of stuff out there, and I suggest you read it. There’s even some stuff advocating not to bother.

Have a look. If you want to know if it really will make a difference, there’s only one way to find out – benchmark performance of your ad-hoc SQL in its application, then replace with a SP and re-benchmark.

This entry was posted in SQLServerPedia Syndication, T-SQL Tuesday and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.