将变量用于UPDATE（）来检查列是否已更新

小编典典

将变量用于UPDATE（）来检查列是否已更新

sql

是否可以在UPDATE（）中使用变量来检查列是否已更新？

这是我的示例代码：

DECLARE @ColumnCount int
DECLARE @ColumnCounter int
DECLARE @ColumnName nvarchar(MAX)

SET @ColumnCounter = 0

SELECT @ColumnCount =  COUNT(c.COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS c  WHERE c.TABLE_NAME = 'Province'
    WHILE @ColumnCount >= @ColumnCounter
    BEGIN
       SELECT @ColumnName = c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c  WHERE c.TABLE_NAME = 'Province' AND c.ORDINAL_POSITION = @ColumnCounter
        IF (UPDATE(@ColumnName))
       SET @ColumnCounter = @ColumnCounter + 1
    END

阅读 234

2021-04-15

共1个答案

小编典典

一些注意事项：您可能想在循环中处理第一次，因为名称在第一次处理中不会更改。您可能要处理@ColumnName为null的情况，因为这意味着您的查询未返回任何行，尽管下面的查询应始终返回一个值。

DECLARE @ColumnCount INT 
DECLARE @ColumnCounter INT 
DECLARE @ColumnName NVARCHAR(max) 
DECLARE @temp varchar(max)

SET @ColumnCounter = 0

SELECT @ColumnCount = Count(c.column_name) 
FROM   information_schema.columns c 
WHERE  c.table_name = 'Province'

WHILE @ColumnCount >= @ColumnCounter 
  BEGIN 
      SET @ColumnName = NULL

      SELECT @ColumnName = c.column_name 
      FROM   information_schema.columns c 
      WHERE  c.table_name = 'Province' 
             AND c.ordinal_position = @ColumnCounter

      IF ( @ColumnName != @temp ) 
        BEGIN 
        --do something 
        END
      SET @temp = @ColumnName
      SET @ColumnCounter = @ColumnCounter + 1 
  END

2021-04-15