Add value to a column in a while loop in SQL Server I have a stored procedure which looks like that :

ALTER PROCEDURE psu_auto_segment_folder_create

@Folder VARCHAR(MAX), /* for example : 'folder1/folder2/folder3' */
@ListId INT

AS BEGIN

SET NOCOUNT ON

DECLARE @FolderName VARCHAR(2048)
DECLARE @SQL VARCHAR(MAX)
DECLARE @ServerIp VARCHAR (15)

SELECT @ServerIp=SQL_IpAddress FROM xxxxxxxxx
(NOLOCK) 
INNER JOIN xxxxxxxxx (NOLOCK)  ON xxxxxxx.Serverid = xxxxxx.Serverid
WHERE xxxx = @ListId AND Enable = 1

IF OBJECT_ID ('tempdb..#Folder') IS NOT NULL DROP TABLE #Folder
CREATE TABLE #Folder (ID INT IDENTITY(1,1) PRIMARY KEY, FolderName VARCHAR(MAX), Rank VARCHAR(256) NULL, Status INT NOT NULL DEFAULT(-1))

INSERT INTO #Folder (FolderName)
SELECT VALUE FROM dbo.f_Split(@Folder, '/')

WHILE (SELECT COUNT(*) FROM #Folder WHERE Status = -1) &gt; 0
BEGIN

    SELECT @FolderName = FolderName
    FROM #Folder
    WHERE ID IN(SELECT MIN(ID) FROM #Folder WHERE Status = -1)

    SET @SQL = '
                DECLARE @IdBranch INT
                DECLARE @Rank VARCHAR(256)

                IF (SELECT COUNT (*) FROM [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxxxx WHERE Name = ''' + @FolderName + ''' AND objectid = 0) = 0
                BEGIN

                    IF OBJECT_ID (''tempdb..#IdBranch'') IS NOT NULL DROP TABLE #IdBranch
                    CREATE TABLE #IdBranch (IdBranch INT)

                    IF (SELECT COUNT(*) FROM #Folder
                    INSERT INTO #IdBranch (IdBranch)
                    EXEC [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxxx @Folder_name = ''' + @FolderName + ''', @Rang = '''', @Table = ''xxxx'' /* This stored procedure give me an ID in return */

                    SELECT @IdBranch = IdBranch FROM #IdBranch

                    SELECT @Rank = rank FROM [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxx WHERE idbranch = @IdBranch

                    UPDATE #Folder
                    SET Rank = @Rank
                    WHERE FolderName = ''' + @FolderName + '''

                    SELECT * FROM #Folder

                END
                ELSE
                BEGIN
                    PRINT ''error''
                END
    '

    EXEC(@SQL)

    UPDATE #Folder
    SET Status = 1
    WHERE FolderName = @FolderName

END

SELECT * FROM #Folder

SET NOCOUNT OFF

END
GO


Now if i run the stored procedure i will get something like 

Id | Foldername | rank | status
--------------------------------
1  | folder 1   | 1    | 1
2  | folder 2   | 2    | 1
3  | folder 3   | 3    | 1


But i want the result to be 

Id | Foldername | rank | status
--------------------------------
1  | folder 1   | 1    | 1
2  | folder 2   | 12   | 1
3  | folder 3   | 123  | 1


Like folder / subfolder / sub-sub-folder

thanks for your help !
Here is an example of how using recursion might solve your problem.

Assuming that my demo table variable @Folder is equal to what comes our of your #folder at the very bottom of your procedure:

--demo setup
declare @Folder Table (
  Id INTEGER,
  Foldername VARCHAR(8),
  rank INTEGER,
  status INTEGER
);

INSERT INTO @Folder
  (Id, Foldername, rank, status)
VALUES
  ('1', 'folder 1', '1', '1'),
  ('2', 'folder 2', '2', '1'),
  ('3', 'folder 3', '3', '1');

--the solution
;
WITH x
AS (
    SELECT id
        ,Foldername
        ,convert(VARCHAR(10), rank) AS rank
        ,STATUS
    FROM @Folder
    WHERE id = 1

    UNION ALL

    SELECT y.id
        ,y.Foldername
        ,convert(VARCHAR(10), convert(VARCHAR(10), x.rank) + convert(VARCHAR(10), y.rank)) AS rank
        ,y.STATUS
    FROM x
    INNER JOIN @Folder AS y
        ON y.Id = x.Id + 1
    )
SELECT id
    ,Foldername
    ,rank
    ,STATUS
FROM x
ORDER BY id
OPTION (MAXRECURSION 10000);




| id | Foldername | rank | STATUS |
|----|------------|------|--------|
| 1  | folder 1   | 1    | 1      |
| 2  | folder 2   | 12   | 1      |
| 3  | folder 3   | 123  | 1      |