> The weakness of this method is that now, b_SP becomes dependant on
> either another stored procedure to create the temporary table, or
> executing statements in your script. There is no way you can now get
> seperate resultsets merely from executing b_SP. (Global temporary
> tables as I outlined in the previous post, have the same problem: they
> will require some sort of wrapper in the executing code or stored
> procedure to maintain dropping and creating the tables.) This creates
> a disadvantage in some scenerios - for instance, if b_SP is already
> used heavily in query/report calls, it might be a pain to modify
> everything that uses it to accomadate a_SP.
There are a couple of ways to address this. Recently when I was a bit
lazy, I wrote in the b_SP of the occasion:
IF object_id('tempdb..#temptbl') IS NULL
Where temptbl.sqlinc includes the declaration of the temp table. Now,
I reckon that not everyone has the access to a preprocessor for T-SQL
(but the above preprocessor is available in the public domain, see
my post about AbaPerls earlier this evening).
But a stricter arrangement would be to push the logic of b_sp to
b2_sp, and a_sp will call b2_sp. The b_sp as you know it will only
create the temp table, call b2_sp, and then return the result set
from the temp table.
As long as you don't have a preprocessor, you still have to maintain
two CREATE TABLE statements (or more, if there are more callers to
b2_sp), but this is in no way different than when you use INSERT..EXEC.
In fact it is somewhat more forgiving, because if you add a column that
d_sp needs, and forget to change a_sp, a_sp may still survive. With
INSERT EXEC, a_sp dies instantly.
(A side note: in the preprocessor I have, you must list in the include-
file which files that references the include-file, so it is very easy
to get hold of all referencing files and changes these.)
Quote:> So I see the only way you can keep the stored procedures independent
> of each other, really, is _insert into #temp exec(a_SP)_.
No, they are not independent, since if you change the result set of
the callee, you MUST change the caller. It has never happened to you
that an INSERT EXEC have failed because of this?
Quote:> I cannot think of a scenerio where I'd want to use the results of b_SP,
> where b_SP isn't already being used somewhere. It is an assumption I
> made though based on my experience...
Well, one reason for this is simply that you are working with some
complex stored procedure, and you find that you want to move some
complex logic to a sub-procedure, and you need to share a temp table
between the two.
Quote:> Which leads another possible solution: incorporate the code of b_SP
> into a_SP directly, forget all this messing around with temp tables
> and execs - just copy and paste code. It leads to maintenance problems
> (if b_SP is wrong, you have 2 SPs to change) but allows for more
> optimized, streamlined code in the end.
If b_SP is a simple SELECT statement, then this path is probably the
way to go. But if b_SP is 200 lines of code, you are asked to be
whacked on the fingers if you do this.
Another possibility which have not discussed is to turn b_SP (or its
core logic) into a table-valued user-defined function.
I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?