August 13, 2004 at 4:06 pm
I have a 'base' stored proc which performs many calculations required by several other stored procs. How can I call this 'base' stored proc (and retrieve the resultset) WITHOUT creating a table in the 2nd proc?
My base stored proc is 'sp_PL_REPORT_Final' and contains 2 fields: A and B. All procs I created which call this 'base' proc contain the following at the beginning of the proc:
CREATE TABLE #Final
(a float, b smalldatetime)
SET NOCOUNT ON
INSERT INTO #FINAL exec sp_pl_report_final
This works ok but whenever I change the fields in my 'base' roc I have to update the table in the other stored procs--currently 10 and increasing
Thanks,
Dan
August 13, 2004 at 8:57 pm
Return a table variable instead? Not sure how you get around this. Don't the other procs use the base proc?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 14, 2004 at 3:45 am
You might consider creating a permanent table
create table final(a float, b smalldatetime)
and keep this current whenever the data changes.
This way the underlying proc would not need to recreate the temporary table each time the reporting procs are executed.
Use the permanent table in the reporting procs.
this will probably boost performance.
Yes when the underlying structure changes, things depending on the old structure will be broken.
/rockmoose
You must unlearn what You have learnt
August 16, 2004 at 7:42 am
How about using a table-returning User-defined Function instead?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply