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?

Triynko
  • 18,766
  • 21
  • 107
  • 173
  • 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. – Sean Lange Mar 21 '18 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. – Triynko Mar 21 '18 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. – Sean Lange Mar 21 '18 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. – Triynko Mar 21 '18 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. – Triynko Mar 21 '18 at 19:21
  • The results are sorted and paged, so the output of phase 2 is a final ordered page of ids. Phase 3 takes that final, minimal, ordered set of ids and joins in complex data, such as real-time aggregate data for just those few returned ids, that would have been too complex to include in the original query, and runs orders of magnitude faster when run for only a few known ids at the end. It's primarily the problem of intersecting and returning (possibly ranked) set of ids in Phase 1 that I'm trying to solve. – Triynko Mar 21 '18 at 19:22
  • Even dumping millions and millions of ids and intersecting them in temp tables to produce a final set, is much, much faster than running a single large read-only query, because the query plan is simply never as smart as one a human can produce with targetted and phased queries. – Triynko Mar 21 '18 at 19:25
  • At the end of the day, all I'm really trying to do is get the final data set back into the original variable, without doing the extra step of writing the data back. And it all seems to boil down to the fact that there's no real way to refer to a known data structure 'by reference' in SQL. Closest thing I can think of that does anything like that is a recursive CTE. – Triynko Mar 21 '18 at 19:28
  • And it's irritating these things are called table 'variables', when they're not variables at all. – Triynko Mar 21 '18 at 19:30
  • Ummm ok. If you think that explanation provided any insight I would urge you to read it again from the perspective of somebody who can't see your screen, does not know your process, does not know what your data is like or what you are trying to do. And no, you can't just set a temp table equal to the result of a query. Just like you couldn't set a table to be the result of a query. Maybe using select into would help you. Who knows? This is so incredibly vague it is impossible to understand. – Sean Lange Mar 21 '18 at 19:30
  • As for the variable part, t-sql is not a programming language. It is a data manipulation language. Maybe you should do whatever your process is in dotnet? You could even do it as a CLR if you want to keep it in the database (well mostly) – Sean Lange Mar 21 '18 at 19:32
  • What is vague about a function that returns a set of uniqueidentifiers? What is vague about calling some number of those functions and intersecting the resulting sets of uniqueidentifiers? What is vague about wanting to stick the results of that intersection back in the original table of the first set you started with? – Triynko Mar 21 '18 at 19:32
  • If @A is a set of unique identifiers, and @B, @C, and @D with the same definition are some other sets of identifiers (returned by some function or honestly whatever, doesn't matter, as long as they end up in a temp table or table variable or even inline). I'm just trying to intersect @A with @B, @C, and @D, and put the results back in @A efficiently. It's not that complicated. – Triynko Mar 21 '18 at 19:35
  • It comes down to understanding the question. And it seems to be very difficult to get you to understand this from a perspective outside your knowledge. You have posted zero code and you are wanting explanations of how to do stuff. I told you how to put data into a table variable, with an insert statement. But then you said that isn't what you want. You know say if @A...blah blah blah. How can anybody help there. What is @A? Is it a table? a delimited string? I sense you are getting frustrated but the problem is nobody understands your question. – Sean Lange Mar 21 '18 at 19:36
  • It becomes complicated because SQL doesn't have a way to get @A intersect @B back into @A without using a third table, and then truncating @A and reinserting. – Triynko Mar 21 '18 at 19:37
  • DECLARE @A TABLE(ID UNIQUEIDENTIFIER, UNIQUE CLUSTERED (ID)) == @B == @C == @D. – Triynko Mar 21 '18 at 19:39
  • When did people lose the ability to think abstractly and demand code? Nevermind. – Triynko Mar 21 '18 at 19:40
  • See finally you posted some actual substance. Wasn't that hard was it? You are correct, You can't use a table in a direct assignment. It is a table variable and you have to treat it like a table. – Sean Lange Mar 21 '18 at 19:42
  • That was in the original post, haha. I've always been talking about a set of uniqueidentifiers and intersecting them. The format of the set or table in question was and always has been a single column table of type uniqueidentifier, holding random, as opposed to sequential) ids, with an clustered/unique index on that column, optimizing it for such inner joins or intersections. Frankly, the background of whether an optional rank column is present is extraneous to the problem. – Triynko Mar 21 '18 at 19:47
  • I'll find a way to make this work, and possibly post a solution, if no one has ever figured out how to do this. – Triynko Mar 21 '18 at 19:48
  • Cool look forward to it. And my very first comment in here stated the same thing. You can't just assign it like that. You have to treat like a table. Best of luck. I look forward to reading your solution. – Sean Lange Mar 21 '18 at 19:53

0 Answers0