User-Defined Table Type insertion sometimes causing conversion error

У меня есть определяемый пользователем тип таблицы с именем tvpInsertedColumns :

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

В хранимой процедуре я пытаюсь сделать это (И @Name, и @Phone VARCHAR) :

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

Сбой с ошибкой:

Ошибка преобразования при преобразовании значение varchar 'Some Name' to data type int.

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

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.

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.

15
задан theChrisKent 23 May 2011 в 04:56
поделиться