17

I have a User-Defined Table Type called 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):

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.

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.

theChrisKent
  • 15,029
  • 3
  • 61
  • 62

1 Answers1

20
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 @name or @phone values are of type integer.

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)
dance2die
  • 35,807
  • 39
  • 131
  • 194
  • 1
    +1 and FYI, it isn't a UDT error but datatype precedence in the UNION ALL before the INSERT happens – gbn May 23 '11 at 04:57
  • This wasn't quite right, but it got me to the solution (and that's all I wanted). The `@Name` and `@Phone` variables never changed types, the problem was that at some point (After my sample cutoff) I was also inserting an integer assuming that there would be an automatic conversion. Casting all non `VARCHAR` types to `VARCHAR(MAX)` fixed the issue. I suspect gbn is correct that this has nothing to do with the UDTs but rather with datatype precedence in `UNION` statements. Thank you both for your help. – theChrisKent May 23 '11 at 05:49