Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Perfect! Exactly what I needed! Thanks for your corrections. –  cocojay Apr 10 '13 at 11:43

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.

share|improve this answer
    
Thank you David for the very detailed alternative solution! I am actually using this solution but within MS Access as I run up a test query to ensure the table is created and if it fails I have the "Rollback" within Access. But your idea of integrating it into the Store procedure is also a viable option! I may run some test to see how the methods differ. Thank you! –  cocojay Apr 11 '13 at 8:48
    
+1 for the alternative idea! –  cocojay Apr 11 '13 at 8:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.