Looping through string, adding all numbers e.g. '123' = 1+2+3, demo with working loop included
You need to set @Total to a default value, otherwise it is NULL and NULL + anything always returns NULL.

Changing this line returns 21 for me:

declare @Total int


to

declare @Total int = 0


That being said, this is not a great thing to try to do on a large number of rows.  If you need to do this on scalar values like you are using there, you may want to consider using a scalar udf for it.  This will NOT scale well though so if you use it against a table be prepared to wait a while.