Reusing a Table Variable from a user defined function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reusing a Table Variable from a user defined function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-05 : 09:32:38

Stephen writes "I have a user defined function that returns a table variable. The function takes a non-trivial amount of time to execute and I have already worked to speed it up. It goes about as fast as is possible.

When I use the function in a Stored Procedure I find I need the table variable returned by the function in more than one query.

For example:

define @ClientID int
set @ClientID = 2122

Select MyItems
from fnMyUserDefinedTableFunc(@ClientID) as TableFunc
where TableFunc.ItemType = 'Wigit'

Select MyItems
from fnMyUserDefinedTableFunc(@ClientID) as TableFunc
where TableFunc.ItemType = 'Tigiw'

Is there a way to do this so I only have to call the user-defined function once? In this situation I would try to combine the queries, however this is a very simplistic and hypothetical situation. My code is iterating through many items and could not be combined into one query.

So my question is can you save off the table reference that the function returns. I know I could copy the data into another table variable, but that seems very wasteful for anything but small tables. Do you have any other ideas?

Thank you for your help."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-05 : 09:36:47

Try this

Select MyItems
from fnMyUserDefinedTableFunc(@ClientID) as TableFunc
where TableFunc.ItemType = 'Wigit' or TableFunc.ItemType = 'Tigiw'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources