CODEWITHSUNDEEP
sql-serverstored-proceduresrecursion
Rob
Rob

Reputation: 221

SQL Server Recursive Stored Procedure

I'm trying to list all files in a virtual directory and it's sub directories. This could be employees in a company with subordinates, this is not a file system. Maybe recursive store procedure may not be the answer.

Scenario:

ParentId is the parent directory and root directory has parentId = NULL... think it's self explanatory.

Now the problem... I want a list files that exist in a directory and its sub directories.

For just one directory I would create a stored procedure:

SELECT * FROM Files Where DirId = ????

So how would I create a stored procedure to include sub directories? At the moment I am using C# code and looping through each directory. I prefer to use a stored procedure... unless you prove me wrong.

Upvotes: 4

Views: 9792

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166396

Have a look at using a CTE.

Something like

DECLARE @Directory Table(
    DirId INT,
    ParentId INT
)
DECLARE @Files Table(
    FileId INT, 
    DirId INT
)

INSERT INTO @Directory SELECT 1, NULL
INSERT INTO @Directory SELECT 2, 1
INSERT INTO @Directory SELECT 3, 1
INSERT INTO @Directory SELECT 4, 2

INSERT INTO @Files SELECT 1, 1
INSERT INTO @Files SELECT 2, 1
INSERT INTO @Files SELECT 3, 2
INSERT INTO @Files SELECT 4, 2
INSERT INTO @Files SELECT 5, 3
INSERT INTO @Files SELECT 6, 3
INSERT INTO @Files SELECT 7, 4
INSERT INTO @Files SELECT 8, 4

;WITH Directories AS (
        SELECT  DirId,
                ParentID
        FROM    @Directory
        WHERE   DirId = 2 
        UNION ALL
        SELECT  d.DirId,
                d.ParentID
        FROM    @Directory d INNER JOIN
                Directories p   ON  d.ParentId = p.DirId
)
SELECT  *
FROM    Directories d INNER JOIN
        @Files f ON d.DirId = f.DirId

Upvotes: 6

AdaTheDev
AdaTheDev

Reputation: 147224

I think what you're asking is not to actually access the file system, but rather you have a directory structure represented in tables in your DB, in which case you can use a recursive CTE something like this:

DECLARE @DirId INTEGER
SET @DirId = 1

;WITH CTEFolders AS
(
SELECT FileId, DirId FROM Files WHERE DirId = @DirId
UNION ALL
SELECT fi.FileId, fi.DirId
FROM CTEFolders c
    JOIN Folders fo ON c.DirId = fo.ParentId = c.DirId
    JOIN Files fi ON fo.DirId = fi.DirId
)
SELECT * FROM CTEFolders

Here's a good MSDN reference on recursive CTEs

Upvotes: 1

maycil
maycil

Reputation: 764

Actually recursive is not a good solution. Dba don't want us to use recursive,

You can use this script to get directory and sub directory

Select d.FileName, f.* from Files f
left Join  Files fsub on fsub.DirId = f.DirId 
inner Join Directory d on d.DirId = f.DirId

the sub directory and directory will list with this script

Upvotes: -1

Related Questions