0

This works to output the string 123456 as:

1
2
3
4
5
6

Code:

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
print @char

SET @index= @index+ 1
END

But when I try to use this code to add those values up when looping through them (1+2+3+4+5+6 = 21), then I do no get a result from SQL, please help

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

declare @Total int 


WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
set @Total = @Total + cast(@char as int)

SET @index= @index+ 1
END

print @Total
  • declare @Total int = 0 :) – dimitar Feb 24 '14 at 13:42
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.