0

Suppose I have a table variable defined as:

DECLARE @FilteredIDs TABLE(ID UNIQUEIDENTIFIER, UNIQUE CLUSTERED (ID))

Functions can return table variables, so supposed I have a function that accepts some input and returns @OutputTable (with identical definition). Now suppose that I want to call that function multiple times in a loop passing it different parameters each time, and each time I want to intersect @FilteredIDs with that @OutputTable and assign the resulting intersected set back to the @FilteredIDs variable, so that when the loop finishes, @FilteredIDs will contain the result of all the intersections of all iterations.

I cannot find any information on how to set the value of a table variable. Once declared, I can only use it like a table, not a table 'variable' (e.g. set @T1 = @T2 does not appear to be a valid statement). I could truncate the table and reinsert data, but since I've already inserted the data into a 3rd table variable, I would just like the first table variable to refer to the 3rd table, without having to truncate the first table and reinsert all the data.

If this is not possible, then might there be a way to rename temp tables in this fashion, so that when the same temp table name is used later in the procedure, it refers to a different table than it did at the start of the procedure?

Improve this question
21
  • 2
    You would be better served if you could modify your function to be an inline table valued function instead so you don't need a loop. But yes you are correct. You have to treat it like a table. So if you are deadset on looping you would have to delete all rows first, then insert the new values for each pass through the loop. Commented Mar 21, 2018 at 18:52
  • I wonder if temp tables are visible to a call to sp_executesql, such that I could rename the temp table, then execute dynamic sql that contains the temp table name. I doubt it. Someone even said 'The code in the sys.sp_rename stored procedure contains an explicit check for temporary tables, and raises an error.' It's like there's no way to avoid redundant work by 'referring' to a table, as opposed to using one with a hard-coded name and repopulating it. Commented Mar 21, 2018 at 18:59
  • You can read a temp table from sp_executesql if the temp table is created first. But you are correct, you can't rename a temp table. Perhaps you can share the details of what you are trying to do and we can help you find a good solution. Commented Mar 21, 2018 at 19:00
  • A query analyzer is simply not intelligent enough to optimize some queries, so a single large complex query is doomed to underperform in spite of having optimal indexes, especially on large data set. So, the query was broken down into 3 phases. Phase 1 runs a variable number of filter functions of arbitrary black-boxed complexity (e.g. full-text searches, status filters, filters based on aggregates, etc.) such that each one's responsibility is simply to return a set of ids for objects matching the filter criteria. The results of each filter are intersected and returned as a single set. Commented Mar 21, 2018 at 19:21
  • The returned set may or may not include a rank column, depending on whether the desired sort field was applicable to one of the filters (e.g. if the sort is on 'Title', then the output of the full-text search might include a rank. When intersected with other filters, this rank takes precedence, and makes it all the way out to the final set.) Phase 2 runs only if no rank was output from phase 1. For example, if the sort is some property of the object that was not involved in filtering, then the resulting id list needs joined back to the object set to include the rank column. Commented Mar 21, 2018 at 19:21
Related questions
2
SQL table variable
3
Table variable in SQL Server
9
In SQL Server, how do I create a reference variable to a table?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.