-1

I use sql server 2014.

I have couple stored procedures, one to drop a column table another to add a column table. Following is the code to drop a column. This is a generic procedure to delete any column in any table.

@Column and @Table are input for the stored procedure

The only way I could find to execute the statement has been to define a tsql string containing the sql instruction to execute with the table name and column name added to the command.

    DECLARE @tsql varchar (MAX)
    SET @tsql = 'ALTER TABLE [dbo].['+@Table+'] DROP COLUMN ' + @Column  
    EXECUTE(@tsql)

I'd like to return -1 if an error occur 2 otherwise as following :

    if @@ERROR <> 0
        BEGIN 
            RETURN(-1)
        END
    ELSE
        BEGIN
            RETURN(2)
        END

how to catch the error if something goes wrong while executing 

    EXECUTE(@tsql)

I tried to use @@ERROR but the execution went through without catching the error.

As an alternative what would be the way to check that a column exists if i am trying to drop a column which does not exist ?

Running the stored procedure I add the following Result: error 4924 "ALTER TABLE DROP COLUMN failed because column 'bla' does not exist in table 'part'."

In addition I d like to return a flag to confirm the change if any.

ALTER PROCEDURE [dbo].[DropColumn]
    -- Add the parameters for the stored procedure here
    @Table varchar(MAX)=NULL, 
    @Column varchar(MAX)=NULL,
    @ColumnModified int OUTPUT
AS
    BEGIN TRY
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    Set @ColumnModified=0
    IF (@Table) IS NULL GOTO Help
    IF (@Column) IS NULL GOTO Help
    -- Insert statements for procedure here
    DECLARE @tsql varchar (MAX)
    SET @tsql = 'ALTER TABLE [dbo].['+@Table+'] DROP COLUMN ' + @Column  
    EXECUTE(@tsql)
    if @@ERROR <> 0
        BEGIN 
            RETURN(-1)
        END
    ELSE
        BEGIN
            RETURN(2)
        END
    Help:
        PRINT 'DropColumn'
        PRINT CHAR(13)
        PRINT 'usage:'
        PRINT CHAR(13)
        PRINT '@Table '
        PRINT CHAR(13)
        PRINT '@Column '
        PRINT CHAR(13)
        PRINT 'output @ColumnModified '
        PRINT CHAR(13)
        RETURN(-1)
    END TRY
    BEGIN CATCH
        SELECT   
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage; 
        RETURN(-1)
    END CATCH

The post here How to check if a column exists in SQL Server table is a partial and undirect answer to my problem since it is peculiar to a specif table and column. In stead of string valyue for tableName I have a variable (same for columnName). How can I transpose the following code using @Column and @Table ?

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END
pascal sautot
  • 375
  • 6
  • 20
  • 1
    Possible duplicate of [How to check if a column exists in SQL Server table](https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-sql-server-table) – Mikhail Lobanov Jun 06 '17 at 17:24
  • What this super universal code needs is the second parameter like @Column = 'id; delete dbo.t;' – sepupic Jun 06 '17 at 19:12
  • @sepupic don't understand the remark – pascal sautot Jun 07 '17 at 06:34
  • 1. The "universal" solution like this is bad by definition 2. It's open to sql injection, when instead of column name someone passes malicious code like I did. In this case your code not only drops the column but can delete the table or maybe drop another database (depends on permissions) – sepupic Jun 07 '17 at 06:38

1 Answers1

1

Not sure that your "issue" is reproducible, but I have confirmed that a TRY CATCH block works:

BEGIN TRY

DECLARE @sql nvarchar(max);

SET @sql = 'ALTER TABLE [dbo].[SomeTable] DROP COLUMN NonExistentColumn'

EXECUTE(@sql);

END TRY

BEGIN CATCH

SELECT @@ERROR

END CATCH

This code returned 4924 for me. Which is not 0.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52