Hi @thom-2 A,

The server isn't externally accessible.  Only a handful of people (i.e. me) have write access/drop table access on this database.  Only our DBA's have administrator (i.e. create database) access.  The SP is just a helper SP for SSIS/SSDT, although it could also be useful in SSMS as well.  Anyone maliciously calling it with SQL Injection parameters would likely get fired.

Having said all that, it's best to develop good coding habits, so below is V2 of my SP.

Questions:

1) I'm not sure if I should have separate schema and table parameters?  That would make it more explicit.  It would also allow unquoted parameters (which is really minor).  What I mean by that is:

uspDropTableV2 schema, table

rather than

uspDropTableV2 'schema.table'  -- the dot means I have to quote the parameter

2) I'm not sure about my approach of using placeholders (eg. {somevar}) in my SQL text, then using the REPLACE function to replace them?  I Googled whether T-SQL had string formatting capabilities, found https://stackoverflow.com/questions/159554/string-format-like-functionality-in-t-sql.  Any feedback on myapproach?  For me, I find it less verbose and with easier quoting than:

SELECT foo = 'This ' + @and + ' That = ''' + @whatever + ''' ... etc.

If there's no negative feedback I will likely use this approach in most of my SP's when appropriate.

I also found this interesting article on static vs. dynamic SQL but AFAIK it doesn't apply to this SP since a query plan isn't generated???  http://www.sommarskog.se/dyn-search.html

3) I'm still trying to wrap my head around RAISERRROR and the correct severity level to use.  For the 2nd example, I'd like the SP to raise a warning and halt, but not have it be as severe as an error.

Here's my updated SP:

ALTER PROCEDURE [dbo].[uspDropTableV2]
( @TableName  VARCHAR(255)
, @debug   BIT = 0
)
AS
BEGIN
  DECLARE @server  VARCHAR(100)
  DECLARE @database VARCHAR(100)
  DECLARE @schema  VARCHAR(100)
  DECLARE @table  VARCHAR(100)
  DECLARE @quoted  VARCHAR(255)

  -- Trap for SQL Injection attacks
  IF @TableName LIKE '%;%' OR
   @TableName LIKE '%--%' OR
   @TableName LIKE '%/*%' OR
   @TableName LIKE '%*/%'
  BEGIN
   RAISERROR(
    'Illegal characters (; -- /* or */) in %s.',18,1,@TableName
    --15600,-1,-1,'uspDropTableV2'
   ) WITH LOG
   RETURN
  END

  -- Parse the tablename
  -- We only want the schema and object name
  -- We do not want to drop tables in other databases or servers
  SELECT @server  = PARSENAME(@TableName,4)
  SELECT @database  = PARSENAME(@TableName,3)
  SELECT @schema  = PARSENAME(@TableName,2)
  SELECT @table   = PARSENAME(@TableName,1)

  -- If server or database were specified raise error and abort
  IF @server IS NOT NULL OR @database IS NOT NULL
  BEGIN
   RAISERROR(
    'Only two-level names (schema.table) are allowed. You cannot drop tables in other databases.',
    12,
    1
   )
   RETURN;
  END;

  -- This technique will create concatenated data with separator
  -- See https://stackoverflow.com/questions/19432370/concat-ws-for-sql-server
  SELECT @quoted =
  STUFF (
   (
    SELECT '.' + v
    FROM (VALUES (QUOTENAME(@schema)), (QUOTENAME(@table))) AS v (v)
    FOR XML PATH (''), TYPE
   ).value('.[1]', 'varchar(max)'),
   1,1,''
  )

  -- Build SQL text
  DECLARE @SQL VARCHAR(1000);
  SET @sql=
'BEGIN TRY
  IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID=OBJECT_ID(N''{quoted}'') AND TYPE=(N''U''))
  BEGIN
   DROP TABLE {quoted};
   PRINT ''{quoted} dropped.'';
  END
  ELSE
  BEGIN
   PRINT ''{quoted} does not exist.'';
  END
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT 
   @ErrorMessage = N''Error dropping {quoted}'' + CHAR(13) + CHAR(10) + ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage, -- Message text.
     @ErrorSeverity, -- Severity.
     @ErrorState -- State.
     );
END CATCH
'
  SET @sql=REPLACE(@sql,'{quoted}',@quoted)

  IF @debug = 1
   PRINT @SQL;

  IF @debug = 0
   EXEC (@sql);
END
GO