Abstraction of table variable declaration

[SiWhite]

Hi all,

I'm trying to simplify the maintenance of my stored procedures using SQL
Server 2000's table variables.

Currently each sp returns multiple result sets (1 to 4) that were using
a large SELECT statement (with multiple table joins) to query the same
subset of data in different ways.

I've managed to abstract the creation of the data subset by using a
table valued udf and I intend to use this in the sp's to simplify the
SELECT statements. My problem is I can't work out how to abstract the
creating of the table variable itself.

Ideally I'd like to do something like:

CREATE PROCEDURE dbo.usp_TheSPName AS
DECLARE @WorkingTable TABLE (dbo.udf_CreateTable())
INSERT INTO @WorkingTable SELECT * FROM dbo.FillTable()

-- run SELECTS here...

This would mean I could maintain the structure and contents of the data
subset in two places rather than 500+!

Any ideas (I'm not dead set on using udf's either..)?
Thanks
SiWhite

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[SiWhite]

Thanks for the advice Jacco.
My profiling would have picked up the performance hit but you've saved
me some pointless work.



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!