I saw this mentioned on Twitter, and watched it while doing some light editing and scheduling. It’s from TechEd 2009 in LA, and includes a number of MVPs and consultants and even a Microsoft SQL Server program manager.
It’s a video, running about 55 minutes, on The Pros and Cons of Stored Procedures. It’s a panel, and they take some questions from the audience, but they kind of run through the pros and cons of why they use, or don’t use in some cases, stored procedures. Having been on a couple of these in the past, and while we have an outline, it’s mostly off the cuff discussion.
I thought it was very thought provoking, listening to people talk about where and why they use stored procedures. The different points of view shouldn’t make you choose to use them or not use them, but to listen to the reasons why they choose stored procedures in many circumstances.
If you write SQL code, or argue with developers about whether to use them, you (and your team) should watch this video, and then have a rational discussion about your differences.
The one surprising thing I heard was from Adam Machanic, a stored procedure advocate, who said that in reporting systems he doesn’t often use them. He wants people to ad hoc be able to query tables. I disagree, but that’s my experience. I’ve had better luck responding to report requests back in the day when tools were less capable.