Convert nvarchar to numeric in MSSQL Server

Your data would appear to have values are not valid numeric values. Use try_convert():

select try_convert(numeric(38, 12), col)

This will return NULL if there is a failure in the conversion.

You can find the values that fail the conversion by doing:

select col
from t
where try_convert(numeric(38, 12), col) is null and col is not null;

You need to use try_convert() wherever you reference the column as a numeric. Converting in the select only applies to the select.

Just to demonstrate Gordon's method in case OP needs:

create table #test(val nvarchar(100));

insert into #test values
('-1.00000'),
('0.000000'),
('0.010000'),
('0.100000'),
('0.500000'),
('00000000'),
('1.000000'),
('1.500000'),
('10.00000'),
('10.50000'),
('100.0000'),
('1000.000'),
('1001.000'),
('1006.000'),
(NULL),
(NULL)

select val, TRY_CONVERT(numeric(38,12),val) as converted_val from #test