将varchar（max）值'TNK_99'转换为数据类型int时转换失败

I am working with MS SQL. I wrote a simple code which goes through columns in my table and find the columns with more than 30% zero value. I will save name of column with more than 30% zero in @array.@count just has number of columns and @column has name of all columns.

我正在使用MS SQL。我写了一个简单的代码,它遍历我的表中的列,并找到零值超过30%的列。我会在@ array中保存超过30%零的列名。@ count只有列数,@ column有所有列的名称。

  DECLARE @array varchar(MAX)
  DECLARE @sql varchar(MAX),
  @column as varchar(MAX)
  SELECT @count = (SELECT COUNT(*) FROM sys.columns WHERE object_id = 
  OBJECT_ID('UVE305_TREND_2.dbo.LOGTNK'))
  WHILE @counT>0
  BEGIN

      SET @column = (SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('UVE305_TREND_2.dbo.LOGTNK') AND column_id = @count)
   SELECT @column

     SET @array = (SELECT COUNT(@column) FROM UVE305_TREND_2.dbo.LOGTNK WHERE 
     @column = 0 )
     select @array

     SET @count= @count- 1
END;
IF @r_count >= CAST(@array AS INT)
     SET @list= @column+','+@list;

when I tried to run my query I got following error:

当我尝试运行我的查询时出现以下错误:

Conversion failed when converting the varchar(max) value 'TNK_99' to data type int.

将varchar(max)值'TNK_99'转换为数据类型int时转换失败。

'TNK_99' is my column name. any help will be appreciated.

'TNK_99'是我的专栏名称。任何帮助将不胜感激。

1 个解决方案

#1

This expression:

WHERE @column = 0

Is being converted to:

正在转换为:

WHERE 'TNK_99' = 0

That is, it is comparing a string (which happens to be a column name) to a number. By the rules of SQL the values are converted to numbers -- and you get a conversion error.

也就是说,它将字符串(恰好是列名)与数字进行比较。根据SQL的规则,值将转换为数字 - 并且您会收到转换错误。

There is no simple way to solve this. The solution involves dynamic SQL, which is rather more complex than your code.

没有简单的方法可以解决这个问题。该解决方案涉及动态SQL,它比您的代码复杂得多。

EDIT:

The code looks like:

代码如下:

DECLARE @sql NVARCHAR(MAX);
WHILE @count > 0
BEGIN
    SET @column = (SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('UVE305_TREND_2.dbo.LOGTNK') AND column_id = @count);

    SET @SQL = 'SELECT @array = COUNT(*) FROM UVE305_TREND_2.dbo.LOGTNK WHERE ' + QUOTENAME(@column) + ' = 0 )'

    EXEC sp_executesql @SQL, N'@array int output', @array=@array output;

    SELECT @column, @array

    SET @count= @count- 1
END;

As with the original code, this assumes that all columns are numeric. This code is a little vague on what "zero value" means. Perhaps you intend:

与原始代码一样,这假定所有列都是数字。这个代码对“零价值”的含义有点模糊。也许你打算:

    SET @SQL = 'SELECT @array = COUNT(*) FROM UVE305_TREND_2.dbo.LOGTNK WHERE ' + QUOTENAME(@column) + ' = ''0'' )'

#1