SQL 用户定义的表类型插入有时会导致转换错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6092390/
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
User-Defined Table Type insertion sometimes causing conversion error
提问by theChrisKent
I have a User-Defined Table Type called tvpInsertedColumns:
我有一个名为tvpInsertedColumns的用户定义表类型：
CREATE TYPE [Audit].[tvpInsertedColumns] AS TABLE(
[ColumnName] [varchar](max) NOT NULL,
[NewValue] [varchar](max) NULL
)
In a Stored Procedure I am attempting to do this (Both @Name and @Phone are VARCHARs):
在存储过程中，我试图这样做（@Name 和 @Phone 都是 VARCHAR）：
DECLARE @AuditColumns Audit.tvpInsertedColumns
INSERT INTO @AuditColumns (ColumnName,NewValue)
SELECT 'Name',@Name UNION ALL
SELECT 'Phone',@Phone
This fails with the error:
这失败并出现错误：
Conversion failed when converting the varchar value 'Some Name' to data type int.
将 varchar 值“Some Name”转换为数据类型 int 时转换失败。
However, in another Stored Procedure I am doing this (@AddressLine1 and @AddressLine1 are VARCHARs):
但是，在另一个存储过程中，我正在执行此操作（@AddressLine1 和 @AddressLine1 是 VARCHAR）：
DECLARE @AuditColumns AS Audit.tvpInsertedColumns
INSERT INTO @AuditColumns (ColumnName,NewValue)
SELECT 'AddressLine1',@AddressLine1 UNION ALL
SELECT 'AddressLine2',@AddressLine2
And everything works just fine.
一切正常。
Both Stored Procedures are just doing a simple insert and then trying to use the type along with another stored procedure that takes the UDT as a parameter.
两个存储过程都只是做一个简单的插入，然后尝试将该类型与另一个将 UDT 作为参数的存储过程一起使用。
This is my first real experience with UDTs, so I hope I'm just missing something obvious, but this makes no sense to me. Let me know if you need further information.
这是我第一次真正体验 UDT，所以我希望我只是遗漏了一些明显的东西，但这对我来说毫无意义。如果您需要更多信息，请告诉我。
回答by dance2die
DECLARE @AuditColumns Audit.tvpInsertedColumns
INSERT INTO @AuditColumns (ColumnName,NewValue)
SELECT 'Name',@Name UNION ALL
SELECT 'Phone',@Phone
I don't know much about UDTs but what I think is happening is that,
at one point, either @nameor @phonevalues are of type integer.
我对 UDT 了解不多，但我认为正在发生的事情是，在某一时刻，@name或者@phone值是整数类型。
Try to cast @Name and @Phone to varchar
尝试将 @Name 和 @Phone 转换为 varchar
INSERT INTO @AuditColumns (ColumnName,NewValue)
SELECT 'Name', cast(@Name as varchar) UNION ALL
SELECT 'Phone', cast(@Phone as varchar)
