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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-30 : 07:46:56
|
| Scott V. writes "Guru,I am trying to find a good way to grab a trivial piece of information after calling a relatively complex inline table valued function, using MS SQL 2005 - the row count - while at the same time, using the result set from the very same function as part of a larger query in a stored procedure that is essentially a "Select" type stored procedure for data fetching.The goal of the outer procedure is essentially limit the data set that is returned to the caller to a subset of rows and to allow dynamic sorting of the data, via a sort parameter.So far, I have been able to come up with three solutions.One would be to call the table value function twice, which I find highly unappealing because I *assume* that the underlying query is executed twice.A second option would be to open a cursor on the result set, and use @@CURSOR_ROWS to get the row count, and then structure the outer procedure using the cursor.The third is to use a #tmp table or table variable.What other approaches could be taken, and of the above, which is the least bad?" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 08:31:47
|
| Store the result in a temp table. This approach gives you the opportunity to add indexes for faster access later.Peter LarssonHelsingborg, Sweden |
 |
|
|
|
|
|
Reusing the results of a Table Valued Function