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.
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.
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.