是否可以在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
最佳答案
一些东西:
您可能要在循环中处理第一次,因为名称在第一次处理中不会更改。
您可能要处理@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