在更新数据库表时获取错误“关键字'SET'附近的语法不正确。”

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:

在我的数据库中的大多数表中,有两列“IsActive”和“IsDeleted”,它们都具有默认键约束和类型BIT我正在尝试执行以下操作:

1. If column IsDeleted exist, copy its negative values of IsDeleted to IsActive.

   如果存在列IsDeleted,则将其负值IsDeleted复制到IsActive。

2. 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.

   如果列IsDeleted存在但不存在IsActive,则创建列IsActive,将IsDeleted的负值复制到IsActive,然后将默认键约束添加到列IsDeleted。

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

3.最后从列IsDeleted中删除默认值约束,然后删除该列。

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'

我正在查询db中的每个表。上面的查询运行正常,并按我的意愿更新每个表,但它也给我错误'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.

我用4个表创建了测试数据库。第一个表有2列:IsActive和IsDeleted,第2个表只有列IsActive,第3个表只有列IsDeleted,第4个表有一个普通的nvarchar列。当执行上面。一切正常,但在SET附近收到错误语法错误。实际上我得到了所需的结果,但也收到了不需要的错误信息。

1 个解决方案

#1

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