How do I write a TSQL function that takes a table name to drop, and drops a table

Answer 1

You can't drop a table with a function: you have to do it in a stored proc. (Functions cannot modify the database.) Something like:

CREATE PROCEDURE SAFE_DROP_TABLE(@TableName nvarchar(30))
AS
BEGIN

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@TableName) 
     AND TYPE IN (N'U'))            
        EXEC ('DROP TABLE ' + @TableName) -- note change here
        IF @@ERROR <> 0 
        BEGIN
            PRINT 'Failed to drop ' + @TableName + ' Error = ' + @@ERROR
        END ELSE BEGIN
            PRINT 'dropped' + @TableName
        END
    ELSE 
        PRINT 'NO ACTION NEEDED'
    END

END

Answer 2

You would need to use EXEC to do this.

On the line that says DROP TABLE, you would do:

EXEC('DROP TABLE ' + @TableName)

BUT, I strongly recommend not doing this. Anyone could put any table name (or any other valid SQL in @TableName (called a SQL injection) and cause you all kinds of problems.

I'd read up on some database theory before getting too deeply into trying to learn T-SQL syntax.

SQL Injection Information:
http://unixwiz.net/techtips/sql-injection.html
http://en.wikipedia.org/wiki/SQL_injection
http://msdn.microsoft.com/en-us/library/ms161953.aspx