How to Drop Tables using a variable in SQL Server?

Question

DETAILS: I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table.

WHY?: In my Access Database I am running a transaction where a bunch of new SQL tables will be created on the fly... but if for some reason one of these tables fails to be created then I need to completely roll back and Drop any of tables that were created in the Back-End.

QUESTION: Is it possible to create a generic stored procedure to drop any SQL table depending on the name I pass through.

NOTE: I have only just started learning SQL code thus I have minimal understanding of SQL code and syntax so describing in layman's terms would be much appreciated.

SO FAR:

    CREATE PROCEDURE dbo.procdroptable
        @TABLENAME SYSNAME
    AS
    BEGIN
        SET NOCOUNT ON;
    DECLARE @ SQL NVARCHAR(MAX)
    SELECT @ SQL 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';

    EXEC Sp_Executesql @SQL;
    END
    GO

Answer 1

Without questioning your motives, just looking at the stored proc's code, you need to make one or 2 small adjustments as follows:

CREATE PROCEDURE dbo.procdroptable
    @TABLENAME SYSNAME
AS
 BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';
    EXEC sp_executesql @SQL;
 END
GO

You can then execute this like so: exec dbo.procdroptable @TABLENAME = 'yourtablename'

Note: this proc also assumes that all your tables will belong to the [dbo] schema.

Answer 2

This isn't a direct answer to your question, RoKa already has that covered, though might be a useful alternate solution to cover your requirement.

I'm not sure what difference it will make that you are using Access as a front-end to an MSSQL database, over using the DB by other means, but in MSSQL's back-end many structural changes are transactional in the same way that data changes are so you may not need to do any manual clean-up if you can use built-in transactions this way.

For instance:

-- begin explicit transaction
BEGIN TRANSACTION
-- create table and test (note: a "real" table not one in tempdb or a variable)
CREATE TABLE test_tran (intcol INT)
INSERT test_tran VALUES (1)
INSERT test_tran VALUES (2)
SELECT * FROM test_tran
-- rollback transaction
ROLLBACK TRANSACTION
-- this will error as the transaction rolled back so it is as if the table was never created
SELECT * FROM test_tran

The above gives the following output:

(1 row(s) affected)
(1 row(s) affected)
t
1
2
(2 row(s) affected)
Msg 208, Level 16, State 1, Line 13
Invalid object name 'test_tran'.

This will work the same way if there is an implicit rollback due to error like with this example with a forced error:

-- begin explicit transaction
BEGIN TRANSACTION
SET XACT_ABORT ON
-- create table and test (note: a "real" table not one in tempdb or a variable)
CREATE TABLE test_tran (intcol INT)
INSERT test_tran VALUES (1)
INSERT test_tran VALUES (2)
SELECT * FROM test_tran
-- divide-by-zero error will force rollback
INSERT test_tran VALUES (2/0)
COMMIT TRANSACTION
-- split batch or XACT_ABORT will cause everything to the end to be skipped
GO
-- this will error
SELECT * FROM test_tran

If you remove the forced error from the above example the COMMIT takes effect and the table survives (as would any other structural or data updates), with the error in place the table is removed as the transaction is cancelled.

Of course this may not be practical depending on other needs: if the process is very long running and touches many parts of an active database then you will need to be careful of wrapping the whole thing in a single explicit transaction as you could be holding locks for a long time that would disrupt other users (so dealing with consistency issues due to errors more manually might be more desirable than using an explicit transaction) and if you need to keep some of the side-effects of the process even after rolling it back on error (i.e. you want to keep information added to log tables during the process) you might not be able to use this method.