Getting error "Incorrect syntax near the keyword 'SET'." while Updating the db tables

Question

I am running following query which is not working properly:

create table #temp(
 tableName nvarchar(50)
)
insert into #temp
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @name VARCHAR(50)
DECLARE @TQ VARCHAR(500)
DECLARE @ContainName VARCHAR(500)
DECLARE db_cursor CURSOR FOR  
SELECT tableName 
FROM #temp

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
      select @name IF COL_LENGTH(@name,'IsDeleted') IS NOT NULL
      begin
        IF COL_LENGTH(@name,'IsActive') IS NOT NULL
        begin
            exec ('UPDATE '+@name+' SET  [IsActive] = ~[IsDeleted]')

        end      
        ELSE
        BEGIN
            exec('ALTER TABLE '+@name+' ADD IsActive bit')
            exec ('UPDATE '+@name+' SET  [IsActive] = ~[IsDeleted]')
            exec('ALTER TABLE '+@name+' ALTER COLUMN IsActive SET DEFAULT ''true''')

        END
        if exists(select * from sysobjects o inner join syscolumns c on o.id = c.cdefault inner join sysobjects t on c.id = t.id where o.xtype = 'D' and c.name = 'IsDeleted' and t.name = @name)
            begin
                set @ContainName=(select o.name from sysobjects o inner join syscolumns c on o.id = c.cdefault inner join sysobjects t on c.id = t.id where o.xtype = 'D' and c.name = 'IsDeleted' and t.name = @name)
                exec('ALTER TABLE ' + @name + ' drop constraint ' + @ContainName)
            end
        exec('ALTER TABLE '+@name+' DROP COLUMN [IsDeleted]')
      end

      FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor


drop table #temp

In most of the tables in my db there are two columns "IsActive" and "IsDeleted" with both of them having default key constraint and of type BIT I am trying to do following:

If column IsDeleted exist, copy its negative values of IsDeleted to IsActive.
If column IsDeleted exist but not IsActive, then create column IsActive, copy negative values of IsDeleted to IsActive, then add default key constraint to the column IsDeleted.

3.Finally remove the default value constraint from the column IsDeleted and then drop that column.

I am running above query for each table in db. The above query is running fine and updating each table as I want, but its also giving me error 'incorrect syntax near SET'

I created test db with 4 tables. 1st table with 2 columns: IsActive and IsDeleted, 2nd Table with only column IsActive, 3rd table with only column IsDeleted, and 4th table with one normal nvarchar column. When executed above. All worked fine, but getting error Incorrect Syntax near SET. Actually I am getting the desired result but also getting unwanted error message.

you could use a sql profiler to see what gets actually executed and see the statement that produces the error ... maybe it's something with your tablenames ... might help if you would put the table names in [] when you create your sql statements ... — PrfctByDsgn

Giorgi Nakeuri Giorgi Nakeuri · Accepted Answer · 2015-02-06T07:50:26

This works for me:

DECLARE @name VARCHAR(50)
DECLARE @TQ VARCHAR(500)
DECLARE @ContainName VARCHAR(500)

DECLARE db_cursor CURSOR
FOR
    SELECT  TABLE_NAME
    FROM    INFORMATION_SCHEMA.TABLES
    WHERE   TABLE_TYPE = 'BASE TABLE'

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0
    BEGIN   
        IF COL_LENGTH(@name, 'IsDeleted') IS NOT NULL
            BEGIN
                IF COL_LENGTH(@name, 'IsActive') IS NOT NULL
                    BEGIN
                        EXEC ('UPDATE '+@name+' SET  [IsActive] = 1 - [IsDeleted]')
                    END      
                ELSE
                    BEGIN
                        EXEC('ALTER TABLE '+@name+' ADD IsActive bit')
                        EXEC ('UPDATE '+@name+' SET  [IsActive] = 1 - [IsDeleted]')
                        EXEC('ALTER TABLE '+@name+' ADD CONSTRAINT DF_'+@name+' DEFAULT(0) for [IsActive]')

                    END
                IF EXISTS ( SELECT  *
                            FROM    sysobjects o
                                    INNER JOIN syscolumns c ON o.id = c.cdefault
                                    INNER JOIN sysobjects t ON c.id = t.id
                            WHERE   o.xtype = 'D'
                                    AND c.name = 'IsDeleted'
                                    AND t.name = @name )
                    BEGIN
                        SET @ContainName = ( SELECT o.name
                                             FROM   sysobjects o
                                                    INNER JOIN syscolumns c ON o.id = c.cdefault
                                                    INNER JOIN sysobjects t ON c.id = t.id
                                             WHERE  o.xtype = 'D'
                                                    AND c.name = 'IsDeleted'
                                                    AND t.name = @name
                                           )
                        EXEC('ALTER TABLE ' + @name + ' drop constraint ' + @ContainName)
                    END
                EXEC('ALTER TABLE '+@name+' DROP COLUMN [IsDeleted]')
            END

        FETCH NEXT FROM db_cursor INTO @name   
    END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Getting error "Incorrect syntax near the keyword 'SET'." while Updating the db tables

1 Answers