用户定义的表类型插入有时会导致转换错误 [英] User-Defined Table Type insertion sometimes causing conversion error

问题描述

我有一个名为 tvpInsertedColumns 的用户定义表类型:

I have a User-Defined Table Type called tvpInsertedColumns:

CREATE TYPE [Audit].[tvpInsertedColumns] AS TABLE(
    [ColumnName] [varchar](max) NOT NULL,
    [NewValue] [varchar](max) NULL
)

在存储过程中，我正在尝试执行此操作(@Name 和 @Phone 都是 VARCHAR):

In a Stored Procedure I am attempting to do this (Both @Name and @Phone are VARCHARs):

DECLARE @AuditColumns Audit.tvpInsertedColumns
INSERT INTO @AuditColumns (ColumnName,NewValue)
    SELECT  'Name',@Name UNION ALL
    SELECT  'Phone',@Phone

失败并出现错误:

转换时转换失败varchar 值 'Some Name'到数据类型 int.

Conversion failed when converting the varchar value 'Some Name' to data type int.

但是，在另一个存储过程中，我正在执行此操作(@AddressLine1 和 @AddressLine1 是 VARCHAR):

However, in another Stored Procedure I am doing this (@AddressLine1 and @AddressLine1 are VARCHARs):

DECLARE @AuditColumns AS Audit.tvpInsertedColumns
INSERT INTO @AuditColumns (ColumnName,NewValue)
    SELECT  'AddressLine1',@AddressLine1 UNION ALL
    SELECT  'AddressLine2',@AddressLine2

而且一切正常.

两个存储过程都只是做一个简单的插入，然后尝试将类型与另一个将 UDT 作为参数的存储过程一起使用.

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.

推荐答案

DECLARE @AuditColumns Audit.tvpInsertedColumns
INSERT INTO @AuditColumns (ColumnName,NewValue)
SELECT  'Name',@Name UNION ALL
SELECT  'Phone',@Phone

我对 UDT 了解不多，但我认为正在发生的事情是，在某一时刻，@name 或 @phone 值是整数类型.

I don't know much about UDTs but what I think is happening is that, at one point, either @name or @phone values are of type integer.

尝试将 @Name 和 @Phone 转换为 varchar

Try to cast @Name and @Phone to varchar

INSERT INTO @AuditColumns (ColumnName,NewValue)
SELECT  'Name', cast(@Name as varchar) UNION ALL
SELECT  'Phone', cast(@Phone as varchar)

这篇关于用户定义的表类型插入有时会导致转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！