title: Stored procedure to drop table
body: <p>I have created a stored procedure that will drop a table if it exists in a database. When running the stored procedure with EXEC, I am getting the following error: </p>
  
  <blockquote>
    <p>Msg 203, Level 16, State 2, Procedure
    sp_DropIfExists, Line 13 The name 'IF
    EXISTS(SELECT 1 FROM sys.objects WHERE
    OBJECT_ID = OBJECT_ID(N'table_name')
    AND type = (N'U')) DROP TABLE
    [table_name]' is not a valid
    identifier.</p>
  </blockquote>
  
  <p>However if i copy and paste the T-SQL that is generated into management studio, it seems to be running fine. Can someone explain why this is not valid? The fix would be nice, but I am really after the Why primarily, The How would be nice to though! Thanks in advance. </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_DropIfExists](@tableName VARCHAR(255)) 
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @SQL VARCHAR(MAX);
      SET @SQL = 'IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''' + @tableName + ''') AND type = (N''U'')) DROP TABLE [' + @tableName + ']'
      PRINT @SQL;
      EXEC @SQL;
  END
  </code></pre>