calling EXEC() generates error: could not

问题:

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

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).

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().

Here's my code:

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

...

Here's the error:

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

回答1:

EXEC without brackets attempts to call a procedure.

Try EXEC(@dynDelete)

回答2:

You need to write
EXEC(@dynDelete)

回答3:

Use EXEC() or EXECUTE() to execute your Sql Query as below:

SET @dynDelete  = 'DELETE FROM ' + @dynTab + ' WHERE id = ' + @id 
EXEC(@dynDelete)

OR

SET @dynDelete  = 'DELETE FROM ' + @dynTab + ' WHERE id = ' + @id 
EXECUTE(@dynDelete)

回答4:

Delete DeleteCommandType from SqlDataSource if used EXEC