In my career I’ve worked with stored procedures in a way I think many people never do. For example I’ve worked on projects where both the data access and the business logic have been placed in the database in the form of stored procedures. Not quite your standard three-tier solution but it works quite well when the people with all the business knowledge also knows a lot about databases and SQL but less about anything else. Obviously this involves a lot of data being passed around between stored procedures.
This means that I’ve seen a few different ways to pass data around and I’m sad I didn’t find this page earlier. Sommerskog’s description of the pros and cons with different approaches would definitely have saved be some time. Over the years I’ve tried output parameters, cursors, tables, user defined functions and the table data type. And as usual the latest technology is not the best for all situations but may simplify things that was cumbersome to accomplish before.
And some times the simplest solution is the most brilliant. A common problem I’ve faced is that I have one SP (stored procedure) returning some data as a select statement. Later on I want to reuse that SP in several other SPs. Previously I’ve been using functions or temporary tables to do this which both have their disadvantages, rewriting the original SP being one. But with the insert-exec pattern I can reuse the SP right away. I’m embarrassed I didn’t think of that before. Let’s say it is so obvious you don’t think about it. In the same way you forget to look for your glasses on your nose. It’s no silver bullet and it wouldn’t have solved all my problems but it is yet another tool to use.
So if you’re working with databases you should definitely take a closer look at what Sommerskog is writing because knowing things like that will definitely make your life easier. And there is no point in learning it the hard way by your self.