T-SQL无法删除存储过程中的临时表

Question

我读过其他关于删除临时表问题的帖子，似乎没有一篇与这种特定场景相匹配。

下面的存储过程有效，但是

Exec RefreshCustShip

生成大量错误：

无法删除表#tempxxx，因为它不存在或您没有权限。

它怎么会不存在？我将在@@RowCount > 0上进行。

CREATE PROCEDURE [dbo].[RefreshCustShip]
AS
BEGIN
SET NOCOUNT ON
DELETE FROM CustShip
DECLARE @CustNo    VARCHAR(255),
@ShipToIds VARCHAR(255),
@val VARCHAR(255),
@Str VARCHAR(255),
@x   VARCHAR(255)
DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR
SELECT A#Cust#No, SHIP#TO#IDS
FROM dbo.Cust
--ORDER BY ModelID
OPEN C
FETCH NEXT FROM C INTO @CustNo, @ShipToIds;
WHILE @@FETCH_STATUS = 0
BEGIN
-- do work here BEGIN CODE BLOCK
SELECT @ShipToIds = RIGHT(@ShipToIds, Len(@ShipToIds) - 1);
WHILE LEN( @ShipToIds ) > 0 
BEGIN 
IF CHARINDEX( '~', @ShipToIds ) > 0 
SELECT @val = LEFT( @ShipToIds, CHARINDEX( '~', @ShipToIds )  - 1 ) ,
@ShipToIds = RIGHT( @ShipToIds, LEN( @ShipToIds ) - CHARINDEX( '~', @ShipToIds ) ) 
ELSE 
SELECT @val = @ShipToIds, @ShipToIds = SPACE(0);
SELECT @str = 'Select * into #tempxxx from ShipTo where A#CUST#NO = ' + '''' + @CustNo + '''' + 'AND SHIP#TO#ID = ' + '''' + @val + ''''
EXEC(@str)
IF @@ROWCOUNT > 0 
BEGIN
SELECT Str = 'INSERT CustShip VALUES (' + '''' + @CustNo + '''' +  ', ' + '''' + @val + '''' + ',1)';
EXEC(@Str);
DROP TABLE #tempxxx         /* PROBLEM */  /* !!!!!!!!! */
END
END
-- do work here END CODE BLOCK
FETCH NEXT FROM C INTO @CustNo, @ShipToIds;
END
CLOSE C;
DEALLOCATE C;
END

Answer 1

您不能使用EXEC创建#表并在该EXEC之外查看它。#表的作用域为当前会话。

运行此示例：

DECLARE @String VARCHAR(200)='CREATE TABLE #TEST(ID INT); INSERT INTO #Test(ID) SELECT 1; SELECT * FROM #TEST;'
EXEC(@String)
SELECT * FROM #TEST

SELECT在EXEC语句内部工作，但在外部运行时出错。如果您需要会话之外的表，请使用##表。

这将是光标内代码的一个不错的替代品，可以避免您遇到的问题。

SELECT @ShipToIds = RIGHT(@ShipToIds, Len(@ShipToIds) - 1);
WHILE LEN( @ShipToIds ) > 0 
BEGIN 
IF CHARINDEX( '~', @ShipToIds ) > 0 
SELECT @val = LEFT( @ShipToIds, CHARINDEX( '~', @ShipToIds )  - 1 ) ,
@ShipToIds = RIGHT( @ShipToIds, LEN( @ShipToIds ) - CHARINDEX( '~', @ShipToIds ) ) 
ELSE 
SELECT @val = @ShipToIds, @ShipToIds = SPACE(0);
IF EXISTS(Select * from ShipTo where A#CUST#NO = @CustNo AND SHIP#TO#ID = @val)
BEGIN
INSERT INTO CustShip VALUES (@CustNo,@val,1);
END
END