Add value to a column in a while loop in SQL Server

Question

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) > 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 !

Answer 1

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 |