Stored procedure to drop table

Question

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:

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.

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.

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

Answer 1

--ALTER (if procedure exists)

CREATE PROCEDURE sp_dropifexists (@tableName VARCHAR(255))

AS

BEGIN

    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 + ']'

    --if write EXEC @SQL without parentheses  sql says Error: is not a valid identifier

    EXEC (@SQL);

END

--test procedure

exec sp_DropIfExists 'table'

Answer 2

Not sure if this will solve your problems but you would be better placing you check is a function like so

CREATE FUNCTION [dbo].[TableExists] 
(
@TableName VarChar(100)
)  
    RETURNS BIT
AS  
BEGIN 
    DECLARE @TableExists BIT

IF EXISTS(SELECT name FROM sysobjects a
          WHERE a.name =  @TableName
          AND a.xtype = 'U')
    SET @TableExists = 1
ELSE
    SET @TableExists = 0


RETURN @TableExists
END

Then you can use it as follows.

IF dbo.TableExists('[table_name]') = 1
     DROP TABLE [table_name]

Try this and let me know if you still get the same error.

Answer 3

you can use sp_execute

sp_executesql @SQL

for more information msdn document link

Answer 4

EXEC @SQL should be EXEC (@SQL). (But @maycil's suggestion is correct too.)

Turns out, without the parentheses @SQL's value is interpreted as the name of a stored procedure to execute, not as a script. (I didn't know that before, but I made a small test to verify that it is indeed so.)