SQL 将 varchar 值转换为数据类型 int 时转换失败 - 但我没有转换任何内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41239781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conversion failed when converting the varchar value to data type int - but I'm not converting anything
提问by 3N1GM4
I am trying to write a stored procedure that takes two parameters: table name and record ID.
我正在尝试编写一个带有两个参数的存储过程：表名和记录 ID。
It must return a record with a specified ID (@FormID) from the table with the specified name (@TableName).
它必须@FormID从具有指定名称 ( @TableName)的表中返回具有指定 ID ( )的记录。
I get this error:
我收到此错误：
Conversion failed when converting the varchar value 'SELECT * FROM [Form12_AuditLog] WHERE [FormID] = ' to data type int."
将 varchar 值 'SELECT * FROM [Form12_AuditLog] WHERE [FormID] = ' 转换为数据类型 int 时转换失败。”
I can't really understand the issue because I'm not trying to convert anything to data type int.
我无法真正理解这个问题，因为我没有尝试将任何内容转换为数据类型 int。
The parameters passed to the SQL are:
传递给 SQL 的参数是：
@FormID = 88
@TableName = Form12_AuditLog
SQL:
查询语句：
USE [MyDatabase]
GO
/****** Object: StoredProcedure [dbo].[GetAuditLogByFormID] Script Date: 20/12/2016 5:50:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetAuditLogByFormID]
@TableName varchar(50),
@FormID integer
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ActualTableName AS varchar(255)
SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableName
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT * FROM ' + @ActualTableName + ' WHERE [FormID] = ' + @FormID + ';'
EXEC(@sql)
END
回答by 3N1GM4
Try:
尝试：
SELECT @sql = 'SELECT * FROM ' + @ActualTableName + ' WHERE [FormID] = ' + CONVERT(NVARCHAR,@FormID) + ';'
SQL is trying to convert all of the strings you're attempting to concatenate into INTvalues because @FormIDis an INTand you're using the +operator - this is quite common when concatenating values which are a mixture of string and non-string types. You therefore need to explicitly tell SQL that the INTvalue should be treated as a string-type value for the purposes of the concatenation.
SQL 正在尝试将您尝试连接的所有字符串转换为INT值，因为@FormIDis anINT并且您正在使用+运算符 - 这在连接字符串和非字符串类型混合的值时很常见。因此，您需要明确地告诉 SQL，INT为了连接的目的，该值应该被视为字符串类型的值。
回答by J.M Smith
The issue is because you are passing an integer variable (@FormId) into an NVARCHAR string.
问题是因为您将整数变量 ( @FormId)传递到 NVARCHAR 字符串中。
Change the "SELECT @sql" line to be:
将“SELECT @sql”行更改为：
SELECT @sql = 'SELECT * FROM ' + @ActualTableName + ' WHERE [FormID] = ' + CAST(@FormID AS VARCHAR(20)) + ';'
and it should work.
它应该工作。
回答by Susang
Simple way is to declare variable as VARCHARor NVARCHARif you don't have any mathematical calculations and if you are using that for dynamic query.
简单的方法是将变量声明为VARCHAR或者NVARCHAR如果您没有任何数学计算并且您将其用于动态查询。
@FormID VARCHAR(20)
