问题背景
生成和访问固定列布局的数据很容易。您可以预先创建本地临时表,并通过调用存储过程来填充它们。
另一方面,如果您想生成具有动态列布局的数据,则通常必须动态构建SQL语句,并使用“exec sp_executesql”执行它。由于数据布局在运行时未知,因此无法预先创建临时表,在“exec sp_executesql”语句内部创建的任何临时表都绑定到该范围,并在调用返回时消失,因此更难访问数据(即您的选项更有限)。
我的具体情况
我有一个查询需要访问动态生成的表中的数据。
该表由存储过程生成,该存储过程动态构建查询,将其存储在变量“@sql nvarchar(max)”中,并通过调用“exec sp_executesql @statement = @sql”运行它。
该@sql语句类似于"select * into #temptable from...",但是#temptable在"exec sp_executesql"返回时被销毁了。一个快速的解决方法是只使用"##temptable"(即全局临时表),因为它在存储过程返回时仍然存在,并且我可以轻松地在调用范围内访问它(因为它具有已知/静态名称)。
我不喜欢这个解决方案,因为全局临时表在名称冲突方面不是线程安全的,而且我不想必须处理动态生成的唯一名称,因为我最终将不得不使用更多的动态SQL来访问它们...这将使我回到原点,导致数据在存储过程外部无法访问。
我认为返回表变量(通过输出参数)不是一个选项(对于SQL Server 2008也是如此),除非可以在不必定义静态表类型的情况下完成。我的存储过程生成的表是动态的,并且取决于传递的输入参数。
内联表值函数不是一个选项,因为我正在运行代码循环来构建@sql查询并调用"exec sp_executesql"。
多语句表值函数(而不是存储过程)也不是一个选择,因为这种函数必须具有明确定义的表格式,而我正在运行动态SQL以返回一个具有可变列数和列名的表取决于输入参数值。我真正想做的就是将动态查询结果集选择到一个新表中,但我发现很难,因为以上方法都不起作用;特别令人恼火的是局部临时表不是局部会话的,而是局部存储过程的,因此它们在返回后消失。我看到唯一的solution坚持认为使用OPENROWSET是唯一的方法,但是出于与不想包含唯一名称管理代码相同的原因,我不想在存储过程内部混乱连接字符串... 它比应该复杂得多。总之,我只想执行动态SQL生成未知格式的数据集,并能够轻松地从调用范围访问它。