SQL查询错误：转换varchar值时转换失败

I search about the error, but I only find different answers that didn't work for me.

我搜索错误，但我只找到不适合我的不同答案。

I have this query, in SQL Server:

我在SQL Server中有这个查询：

DECLARE @column_name varchar (25),
@data_column int,
@table_name varchar (25)

DECLARE @mySql nvarchar (MAX)

SET NOCOUNT ON;

SET @column_name = 'Excellent'
SET @table_name = 'CSAT'
SET @data_column = 10

SET @mySql = 'INSERT INTO '+@table_name+'('+@column_name+') VALUES('+@data_column+')'
EXEC (@mySql)

When I execute it, it shows me this error:

当我执行它时，它显示我的错误：

Conversion failed when converting the varchar value 'INSERT INTO CSAT(Excellent) VALUES(' to data type int.

转换varchar值'INSERT INTO CSAT（优秀）VALUES（'到数据类型int时转换失败。

All the columns are Int and allow nulls.

所有列都是Int并允许空值。

I have to make a conversion or something? I appreciate your help!

我必须进行转换或其他什么？我感谢您的帮助！

4 个解决方案

#1

@data_column is an int, so you need to convert it to varchar because you are building a string.

@data_column是一个int，因此您需要将其转换为varchar，因为您正在构建一个字符串。

SET @mySql = 'INSERT INTO '+@table_name+'('+@column_name+') 
              VALUES('+ Convert(Varchar(10), @data_column)+')'

When sql server encounters an expression that mixes strings and int, it attempts to convert the string to an int (instead of the the other way around). This is documented here: SQL Server Data Type Precedence

当sql server遇到混合字符串和int的表达式时，它会尝试将字符串转换为int（而不是相反）。这在此处记录：SQL Server数据类型优先级

#2

add some space like this

像这样添加一些空间

SET @mySql = 'INSERT INTO '+@table_name+' ('+convert(varchar(50),@column_name)+') VALUES('+@data_column+')'

#3

The sql is trying to make a sum of your string on line:

sql试图在线生成你的字符串：

SET @mySql = 'INSERT INTO '+@table_name+'('+@column_name+') VALUES('+@data_column+')'

Change the @data_column parameter from int to varchar or use a CONCAT function to create your SQL command:

将@data_column参数从int更改为varchar或使用CONCAT函数创建SQL命令：

SET @mySql = CONCAT('INSERT INTO ',@table_name,' (',@column_name,') VALUES(',@data_column,')')

#4

You should use Parametrised Query to do this, Also use appropriate datatype for object names...

您应该使用参数化查询来执行此操作，并使用适当的数据类型作为对象名称...

something like this....

这样的事......

SET NOCOUNT ON;

DECLARE @column_name  SYSNAME
       ,@data_column  INT
       ,@table_name   SYSNAME
       ,@mySql        NVARCHAR(MAX);

SET @column_name = 'Excellent'
SET @table_name = 'CSAT'
SET @data_column = 10

SET @mySql = N' INSERT INTO '+ QUOTENAME(@table_name) 
          +  N' ('+ QUOTENAME(@column_name) +') '
          +  N' VALUES( @data_column )'


Exec sp_executesql @mySql
                  ,N'@data_column INT'
                  ,@data_column

#1