Error converting data type bigint to varchar.

Question

DECLARE @ID BIGINT
set @ID = 1323
UPDATE School 
SET RegistrationFee = 'fee_' + @ID --<<<<error 
Where SchoolRegistrationId = 123

Error converting data type varchar to bigint.

Answer 1

SQL Server automatically converts the data from one data type to another. This is Implicit conversion. Your script has a process with diffirent types (varchar and bigint). Bigint is an exact numeric type. "Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string." (see. https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine link.)

You should use explicit convertion to force It doesn't occur implicit convertion automaticly. Such as CAST function.

Choose one depend on RegistrationFee column data type in following expressions.

'fee_' + CAST(@ID AS NVARCHAR(25)) 
'fee_' + CAST(@ID AS VARCHAR(25)) 
'fee_' + CAST(@ID AS CHAR(25)) 
'fee_' + CAST(@ID AS NCHAR(25)) 

-- Bigint max value is '9,223,372,036,854,775,807' that has 25 chars.