How to Drop Tables using a variable in SQL Server?

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

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.

StackExchange Database Administrators Q#39627, answer score: 4