调用 EXEC() 生成错误:找不到存储过程 [英] calling EXEC() generates error: could not find stored procedure

问题描述

我需要对一个名字只在运行时才知道的表调用删除.

I need to call a delete on a table who's name will only be known at runtime.

我有一个存储过程，它动态地用表名和条件制定删除语句，然后将该字符串作为参数传递给 EXEC() 函数(-显然，所有这些都在事务中).

I have a stored procedure that formulates the delete statement with tablename and criteria dynamically, and then passes that string as an argument to the EXEC() function (-all of this is within in a transaction, obviously).

当我运行存储过程时出现错误 - 找不到存储过程 - 指的是我动态制定并发送到 EXEC() 的语句.

When I run the stored procedure I get an error - could not find stored procedure - referring to the statement I formulated dynamically and sent to EXEC().

这是我的代码:

DECLARE @dynTab AS varchar(50), @dynDelete AS varchar(255)
    DECLARE @crsr CURSOR
    SET @crsr = CURSOR FAST_FORWARD
    FOR
    SELECT dyn_tablename FROM dyn_tab WHERE dyn_doc_type_id IN (SELECT doc_id FROM tree_tab WHERE id = @id) AND dyn_tablecreated = 1

OPEN @crsr
FETCH NEXT FROM @crsr
INTO @dynTab

WHILE @@FETCH_STATUS = 0 AND @@ERROR = 0
    BEGIN
        SET @dynDelete  = 'DELETE FROM ' + @dynTab + ' WHERE id = ' + @id 
        EXEC @dynDelete

        FETCH NEXT FROM @crsr
        INTO @dynTab
    END

            CLOSE @crsr
        DEALLOCATE @crsr 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            return 0
        END

...

错误如下:

找不到存储过程 'DELETE FROM myTable WHERE id = 1111'

Could not find stored procedure 'DELETE FROM myTable WHERE id = 1111'

推荐答案

EXEC 不带括号尝试调用过程.

EXEC without brackets attempts to call a procedure.

试试EXEC(@dynDelete)

这篇关于调用 EXEC() 生成错误:找不到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！