0

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 !

3

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      |

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.