动态引用表而不使用动态 SQL
在 SQL Server 2008 中，我有一个执行复杂查询的存储过程。我会将结果写入临时表（不是系统临时表，而是手动管理的临时表）。
表的名称是用户 Web 会话 ID 的字符串表示形式。这解决了并发问题。这将传递给存储过程，该存储过程将创建表。为了进行清理，名称将与时间戳一起记录在维护表中。
然而，这个名字显然是动态的。我不想使用动态 SQL，因为这会妨碍复杂查询的任何编译。我知道这正在推动事情，但我想做的是：
DECLARE @TableName varchar(100)
SET @TableName = CAST(@SessionID AS varchar)
INSERT INTO @TableName
SELECT....{complex query}
我该怎么做？
更多信息：
记录集可能有数万行（仅限 ID）。用户可以对此记录集应用进一步的过滤，从而减少它。因此它必须是服务器上的一个表。
In SQL Server 2008, I have a stored procedure that executes a complex query. I will write the results to a temp table (not a system temp table, but a manually managed one).
The name of the table is the string representation of the user's web session ID. This solves concurrency issues. This will be passed to the stored procedure, which will create the table. To clean up, the name will be logged in a maintenance table, along with a timestamp.
However, the name is obviously dynamic. I don't want to use dynamic SQL as this precludes any compilation of the complex query. I know this is pushing things, but what I am looking to do is as follows:
DECLARE @TableName varchar(100)
SET @TableName = CAST(@SessionID AS varchar)
INSERT INTO @TableName
SELECT....{complex query}
How can I do this?
More info:
The recordset could be tens of thousands of rows (IDs only). The user can apply further filtering to this recordset, thus reducing it. Therefore it has to be a table on the server.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以先将复杂查询插入到 #temp 表中，然后使用动态 sql 将其插入到名为 sessionID 的表中。
You could insert your complex query into a #temp table first and then use dynamic sql to insert it into a sessionID named table after.
它如何使用编译时不知道的表来编译查询？据我所知，你想做的事情是不可能的。
为什么不寻找其他解决方案，例如将结果存储在以会话 ID 为键的表的 XML 字段中，每个用户/会话/结果集一行？
您还可以将数据拉回 .NET 并直接将其存储在 Session 中。取决于您使用的会话提供程序，其效果如何。
您还可以将结果存储在临时文件中，并使用时间戳跟踪单个表中的文件，并经常运行批处理作业以清除较旧或不活动会话中的文件。
已添加，基于“更多信息”：
好的。是时候打“过早优化”牌了。 :)
您是否有现有的解决方案已经太慢了？您是否研究过在不缓存大型结果集的情况下简化它的方法？
假设您有：您预计有多少用户/会话？这些ID被隔离和缓存后将如何使用？听起来好像需要它们来进行进一步的查询，这意味着它们的最佳位置是在具有静态名称的表中。
此时我的建议是：将所有结果存储在一个关联表中，例如
create table tempResults(session_id guid, other_id int)。然后，您可以将该表的联接硬编码到其他查询中。How would it compile a query with a table it doesn't know anything about at compile time? As far as I know, what you want to do is not possible.
Why don't you look for other solutions, like storing the results in an XML field in a table keyed on session ID, one row per user/session/result-set?
You also could pull the data back to .NET and store it in Session directly. Depends which session provider you're using how well this will work.
You could also store the results in a temp file and track the files in a single table with a time stamp and run a batch job every so often to clean out files from older or inactive sessions.
Added, based on "More Info":
OK. Time to play the "premature optimization" card. :)
Do you have an existing solution that is already too slow? Have you looked at it for ways to streamline it without caching a large result set?
Assuming you have: How many users/sessions do you expect to have? How will these IDs be used once they are isolated and cached? They sound like they are needed for further queries, which means the best place for them is in a table that has a static name.
At this point my suggestion is: store all the results in an association table like
create table tempResults(session_id guid, other_id int). Then you can hard-code joins to this table into other queries.为什么不创建一个选择的临时表，而是将会话 ID 作为字段包含在内，这将使记录具有唯一性？
Why don't you create a temp table of choice, but include the session ID as a field which will make the records unique?