Nested Insert within SQL Server (To Encapsulate or not to Encapsulate)

Question

I'm having a general discussion with one of my developers about philosophy of structuring TSQL stored procedures.

Let's make a couple assumptions (to avoid responses along the line of "don't put business logic in SPs", etc):

1. Business Logic can be in SPs
2. There is no standard scripted layer of CRUD SPs
3. Complex procedures are executed in single scope within an SP
4. Dynamic SQL is allowed

Essentially my coworker has hit his head against the SQL Server wall of nested inserts in SQL Server. You can nest them beyond a single level.

i.e. :

• PROC C is called by PROC B, the results of which are INSERTED into a temp table #X and operations are performed and then a result set is returned

• PROC B is called by PROC A, the results (our original final select from PROC C + Transformations) of which are INSERTED into a temp table #Z and operations are performed and then a result set is returned

This results in SQL Server throwing an error saying you can't nest inserts into temp tables.

I agree with this logic, assuming our four original premises, it would then simply be permissible to include the logic executed in PROC C directly in PROC B.

My coworker however feels, being a good Object Orientated encapsulator, that by encapsulating the final nested insert the code is more maintainable.

I on the other hand feel that encapsulating code into CRUD operations, which use temp tables, is fundamentally opposed to the declarative and procedural approach to data management inherent in SQL and relational databases.

Ultimately, his argument is that if the logic was simply in one SP, then should the table schema change, all SPs addressing the table would have to change. A valid point, however I feel that is unlikely as once our development has bedded down tables are not subject to ad-hoc change.

My argument is that by encapsulating temp table inserts, for use in n-th degree nesting (if possible - thankfully its not), would result in a case in which all SPs which call that encapsulated procedure (or should we now rather call it a method?) would have to be retested anyway in the event that the centralized encapsulated procedure changes, since unless its outputs are the same, it may well result in a break in the calling parent SP.

I suppose my ultimate question is this, fundamentally is T-SQL a declarative, a procedural or an imperative language. In my opinion it is clearly a mixture --> I suppose if I had to create the recipe I'd say:

10% Declarative (since in principle its declarative) 70% Procedural (In our case, the case in which business processes are in procs) 20% Imperative (Imperative constructs exist - esp. in newer SQL Server versions)

---

p.s. At the moment he's suggesting using OUTPUT parameter in the SP call, rather than:

insert into #temp exec spInsertUpdateTable

I have a whole other dislike for OUTPUT parameters, but I suppose that might have to fly. Any other suggestions?

Answer 1

Not an answer, just some off the top of my head ideas:

Single-values are passed back more efficiently via OUTPUT parameters than by result sets. The value of this decreases as the number of returned values increases, however.

In SQL 2008, you can pass temp tables as parameters. Ok, there's a lot of setup you have to do in order to enable this, but even so it could be worth the effort, as you'd then get your N levels of nested calls.

Easily maintainable code is a holy grail that we should all strive for, since--if there's any justice--the person who supports the code should be its original creator. [Insert Dr. Frankenstien joke here.] This should underscore the benefits of modularized code ("sub-procedures"); if you only have to modify the one nested procedure, and you don't have to change the inputs or outputs, then you shouldn't have to worry (i.e. refactor) about procedures that call or are called by it.

As for declarative vs. procedural vs. imperative, sorry, its been years since college, and I've been in the trenches too long to be able to discuss high-falutin' theories without working Google for an hour.

Answer 2

I guess perhaps this avoids instead of answering the question, but the first thing I would do is examine why there are all these temp tables that need passing around. Most often a well constructed select, insert or update statement can replace reams of temp tables. This is actually the intent of T-SQL - to be able to write a single statement to act on a whole set. So where you say "+ transformations" I go straight to "ah, refactor those temp tables right out of the solution by making a select that does the transformations." Make sense?

Example: see if you can produce the result you want with two nested views instead of stored procs. No problem passing results between views (that's a bit of a misnomer anyway) PLUS you get query optimization across the whole operation (SQL Server will expand the view defs and optimize the whole thing at once)

Answer 3

If you are still using SQL Server 2000, I recommend reading this knowledge base article: http://support.microsoft.com/kb/243586/.

Section: "Recompilations Due to Certain Temporary Table Operations Use of temporary tables in a stored procedure may cause the stored procedure to be recompiled every time the procedure is executed.

To avoid this, change the stored procedure so that it meets the following requirements: All statements that contain the name of a temporary table refer to a temporary table created in the same stored procedure, and not in a calling or called stored procedure, or in a string executed using the EXECUTE statement or sp_executesql stored procedure."

I ran into this in a production system under load and it caused some significant performance problems.