I would like to use a column name in a variable as follows:
Declare @amount numeric(25,0)
Declare @ColumnName varchar(20)
Set @ColumnName= 'Amount1'
SELECT @amount = @ColumnName FROM Sale WHERE year=1998
---error: convertion from varchar to numeric
SELECT @amount = Amount1 FROM Sale WHERE year=1998
--- is running fine
How can I fix this syntax problem?
Any advice will be very much appreciated.