如果列已更新，则使用变量 UPDATE() 检查器 [英] Using variable for UPDATE() the checker if the column is Updated or not

问题描述

是否可以在 UPDATE() 中使用变量来检查列是否更新?

Is it possible to use a variable in UPDATE() which check if a column is updated or not?

这是我的示例代码:

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

推荐答案

一些事情:您可能希望通过循环处理第一次，因为第一次通过时名称不会更改.您可能希望在 @ColumnName 为 null 时进行处理，因为这意味着您的查询没有返回一行，尽管您下面的查询应该始终返回一个值.

A few things: You may want to handle the first time through the loop because the name would not have changed the first time through. You may want to handle when @ColumnName is null because that would mean your query did not return a row although your query below should always return a value.

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 

这篇关于如果列已更新，则使用变量 UPDATE() 检查器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！