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.
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)