This is good. A couple things to note:

First, the length of the input variable (@i) is limited to the size of the two varchar declarations. For example, say @i = 99999999999 (that's eleven 9's), you would get an overflow error when converting the expression to varchar...

No big deal; who cares?... Just change the varchars to varchar(20) or varchar(50) and declare @i as bigint. There. Problem solved!!!

Nope. Still have one thing to address and this WILL NOT produce an error[/b]. Instead you will just an incorrect aggregation. For the agregation to be accurate you would have to add additional values to your CTE. Again, say @i = 99999999999 (11 9's) you would return a 90(incorrect) instead of 99(correct). To fix this you would have to add an (11). If @i was 20 characters long you would have to add (11),(12)...(20).

A better way to write this would be:

DECLARE @i BIGINT=99999999999;

with nums(n) as

(

SELECT 1

UNION ALL

SELECT n+1 FROM nums WHERE n<30

)

select sum(convert(int,substring(convert(varchar(30),@i),n,1)))

from nums where n <= len(convert(varchar(30),@i));

Now @i can be 18 characters long (limited to 18 because of the bigint). How about we change @i to varchar(50). Now it works for a number that's 50 chars long.

Instead of:

select n from (values (1),(2),(3),(4)...(50))nums(n)

We are using some recursion:

with nums(n) as

(

SELECT 1

UNION ALL

SELECT n+1 FROM nums WHERE n<LEN(@i)

)

... and then we pull it all together:

DECLARE @i varchar(50)='99999999999999999999999999999999999999999999999999';

with nums(n) as

(

SELECT 1

UNION ALL

SELECT n+1 FROM nums WHERE n<LEN(@i)

)

select sum(convert(int,substring(convert(varchar(50),@i),n,1)))

from nums where n <= len(convert(varchar(50),@i));

GO