问题描述
我需要对一个名字只在运行时才知道的表调用删除.
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'
推荐答案
EXEC
不带括号尝试调用过程.
EXEC
without brackets attempts to call a procedure.
试试EXEC(@dynDelete)
这篇关于调用 EXEC() 生成错误:找不到存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!