时间戳

首页 > 解决方案 > T-SQL 无法删除存储过程中的临时表

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

问题描述

我已阅读有关删除临时表问题的其他帖子，但似乎没有一个与此特定情况相匹配。

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

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

标签： sql-servertsqlstored-procedures

解决方案

您无法使用 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

推荐阅读