主页 / dba / 问题 / 249221
Accepted
user2618935
Asked: 2019-09-20 11:36:09 +0800 CST

使用动态 SQL 创建临时表 SQL Server 13.0.5426 不起作用

  • 772

我正在尝试使用动态 sql (MS SQL Server 13.0.5426) 创建一个临时表。这是一个简化的例子：

使用直接 SQL 可以：

DROP TABLE #tmp_ts; -- will error, but ignored on the first execution
SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts;
SELECT * FROM #tmp_ts;

通过动态 SQL 创建它不起作用。

DROP TABLE #tmp_ts; -- drop from previous test.
EXEC sp_executesql N'SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts';
SELECT * FROM #tmp_ts;

输出：

（受影响的 1 行）
消息 208，级别 16，状态 0，第 63 行无效的对象名称“#tmp_ts”。

我的解决方法是不使用临时表，但我很好奇是否有人知道为什么这不起作用。

谢谢！

sql-server dynamic-sql

3 个回答

  1. 这从来没有奏效。

    如果要动态插入静态临时表，可以这样做：

    CREATE TABLE #t(id INT);

DECLARE @sql NVARCHAR(MAX) = N'SELECT 1 FROM sys.databases;';

INSERT #t ( id )
EXEC sys.sp_executesql @sql;

SELECT * FROM #t AS t;

    如果您需要动态构建临时表，请参阅此问答：

    • 3
  2. Best Answer

    问题是您的临时表的范围是您当前的连接。当您EXEC sp_executesql看不到您的本地临时表时。

    您可以使用全局临时表，使用两个# 而不是一个来表示。即##tmp_ts。但是，对于您的小代码示例，没有理由使用动态 SQL。如果您只是想在插入临时表之前确保它不存在，则可以使用OBJECT_ID元数据功能

    if object_id('tempdb..#tmp_ts') is not null
drop table #tmp_ts

select
...
into #tmp_ts
...
    • 2

  3. 给定下一个示例：

    CREATE TABLE Appointments
(
    fromDate datetime,
    toDate   datetime,
    appointmentMode int
);

INSERT INTO Appointments VALUES
('20190919 11:00:00', '20190919 11:45:00', 0),
('20190919 11:35:00', '20190919 12:00:00', 1),
('20190919 11:00:00', '20190919 12:00:00', 2);

    您可以将 SELECT 查询添加到动态查询：

    DECLARE @CMD NVARCHAR(MAX);

SET @CMD = 'SELECT * INTO #T FROM Appointments; SELECT * FROM #T';

EXEC sp_executesql @CMD;

    或使用全局临时表：

    DECLARE @CMD2 NVARCHAR(MAX);

SET @CMD2 = 'SELECT * INTO ##T2 FROM Appointments;';

EXEC sp_executesql @CMD2;

SELECT * FROM ##T2;

    从日期 | 到日期 | 约会模式
:----------------- | :----------------- | --------------:
19/09/2019 11:00:00 | 19/09/2019 11:45:00 | 0
19/09/2019 11:35:00 | 19/09/2019 12:00:00 | 1
19/09/2019 11:00:00 | 19/09/2019 12:00:00 | 2

    db<>在这里摆弄

    引用自 MS-Docs（粗体是我的）

    sp_executesql 在批处理、名称范围和数据库上下文方面与 EXECUTE 具有相同的行为。在执行 sp_executesql 语句之前，不会编译 sp_executesql @stmt 参数中的 Transact-SQL 语句或批处理。然后，@stmt 的内容被编译并作为一个执行计划执行，该执行计划与调用 sp_executesql 的批处理的执行计划分开。sp_executesql 批处理不能引用在调用 sp_executesql 的批处理中声明的变量。sp_executesql 批处理中的本地游标或变量对调用 sp_executesql 的批处理不可见。数据库上下文中的更改仅持续到 sp_executesql 语句的末尾。

    • 0

相关问题