Incorrect syntax SQL

Question

Why do I get this error when I try to execute the following code? I have a table NewTable1 with two columns: column1 and column2.

I get this error: Incorrect syntax near 'column2'.

--DROP COLUMN PROCEDURE

CREATE PROCEDURE DropColumn
    @tableName varchar(50),
    @columnName varchar(50)
AS

    BEGIN
        DECLARE @SQL nvarchar(500);
        SET @SQL = N'ALTER TABLE ' + QUOTENAME(@tableName) 
         + ' DROP COLUMN ' + QUOTENAME(@columnName);
        EXEC sp_executesql @SQL;
    END
RETURN 0
GO

USE SKI_SHOP;
EXEC DropColumn 'NewTable1', 'column2';
GO

Answer 1

Use appropriate data types. Also You will only be able to drop Columns for tables in callers default schema. Since procedure doesn't take schema into consideration, therefore you can only pass the table name and if a table exists in other than caller default schema they wont be able to delete it using this procedure .

CREATE PROCEDURE DropColumn
    @tableName  SYSNAME,
    @columnName SYSNAME
AS
BEGIN
  SET NOCOUNT ON;

 DECLARE @SQL NVARCHAR(MAX);

 SET @SQL = N' ALTER TABLE ' + QUOTENAME(@tableName) 
          + N' DROP COLUMN ' + QUOTENAME(@columnName);

      EXEC sp_executesql @SQL;
 END
GO

I over looked some basic simple issues in my first approach, whenever creating of Dropping objects in SQL Server always check if they exist, to avoid any errors . A more complete and safe approach would be something like ...

This time I have also added schema as a parameter.

ALTER PROCEDURE DropColumn
    @tableName  SYSNAME,
    @columnName SYSNAME,
    @Schema     SYSNAME,
    @Success    BIT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

 DECLARE @SQL NVARCHAR(MAX);

 SET @SQL = N' IF EXISTS (SELECT * FROM sys.tables t 
                          INNER JOIN sys.columns c 
                          ON t.[object_id] = c.[object_id]
                          INNER JOIN sys.schemas sc
                          ON t.[schema_id] = sc.[schema_id]
                          WHERE t.name  = @tableName
                            AND c.name  = @columnName
                            AND sc.name =  @Schema)
                BEGIN
                  ALTER TABLE ' +  QUOTENAME(@Schema)+ '.' + QUOTENAME(@tableName) 
           + N'   DROP COLUMN ' + QUOTENAME(@columnName)
           + N'   SET @Success = 1; '
           + N' END 
               ELSE
                BEGIN
                  SET @Success = 0; 
                END '

  EXEC sp_executesql @SQL
                     ,N'@tableName SYSNAME, @columnName SYSNAME, @Schema SYSNAME, @Success BIT OUTPUT'
                     ,@tableName
                     ,@columnName
                     ,@Schema
                     ,@Success OUTPUT
 END
GO