Error converting data type varchar to bigint during update

Question

I need to update a bigint value in a database with a concatenation of two strings. SQL does not like this. DailyArchive.LogicalAccount is a bigint, @padding and a.AccountNumber are varchars.

Update dbo.DailyArchive 
SET DailyArchive.LogicalAccount = CONCAT(@padding, a.AccountNumber)
FROM @_AccountList a
JOIN dbo.DailyArchive ON a.AccountNumber = DailyArchive.LogicalAccount

I receive the following error message:

Msg 8114, Level 16, State 5, Procedure updateNumbers_ArchiveDB, Line 15 Error converting data type varchar to bigint.

How I am executing the procedure:

declare @p1 dbo.AccountListType
insert into @p1 values(N'Account Number',N'Account Type')
insert into @p1 values(N'7463689',N'Basic')
insert into @p1 values(N'1317893',N'Premium')
insert into @p1 values(N'2806127',N'Basic')

exec updateNumbers_ArchiveDB
@_AccountList=@p1,
@padding=N'111',
@proc_dateStart='2008-01-04 11:24:46',
@proc_dateEnd='2008-01-04 11:24:46'

You're getting this error because some of the data in either @padding, a.AccountNumber, or both is not convertable to BIGINT. Are you sure that neither of those values contains anything that is not a number? — AHiggins
Try select CONCAT(@padding, a.AccountNumber) FROM @_AccountList a JOIN dbo.DailyArchive ON a.AccountNumber = DailyArchive.LogicalAccount - do you see any that contain non-number characters? — Dave.Gugg
Awfully sparse on details here. Either @padding, a.AccountNumber or DailyArchive.LogicalAccount has a row that is not a numeric string value. — Sean Lange
You appear to be inserting the column names to the table -insert into @p1 values(N'Account Number',N'Account Type'), So you are trying to convert ('111' + 'Account Number') to a BIGINT. Remove this insert, and only insert valid data to your table and you should be fine. — GarethD

Christian Phillips Christian Phillips · Accepted Answer · 2014-08-13T14:14:48

Try removing the line...

insert into @p1 values(N'Account Number',N'Account Type')

These will not convert into a BIGINT.

Even if a value is NULL, it would still be OK, since the CONCAT will just ignore it, ie...

CONCAT(NULL,'111') will give 111.

Error converting data type varchar to bigint during update

1 Answers