IF Exists(SELECT * from Sys.procedures WHERE object_id = OBJECT_ID(N'dbo.GetFoldersRecursive'))
DROP PROCEDURE dbo.GetFoldersRecursive
GO

CREATE PROCEDURE dbo.GetFoldersRecursive (
@FolderID Int -- Not Null, > 0
)
AS
BEGIN
DECLARE @folderPath nvarchar(255);
DECLARE @PortalID int;
SELECT @folderPath = FolderPath + N'%',
@PortalID = IsNull(PortalID, -1)
FROM dbo.Folders
WHERE FolderID = @FolderID;
SELECT *
FROM dbo.Folders
WHERE IsNull(PortalID, -1) = @PortalID
AND FolderPath Like @FolderPath
ORDER BY FolderPath;
END; --Procedure
GO

Exec dbo.GetFoldersRecursive 1;

Posted By Mark Buelsing on 09 Aug 2019 04:06 PM

I'm looking for a starting point to write a SP to retrieve all folders and subfolders of a ParentID. I believe I have seen folder-pickers that have obviously figured out this logic. I have looked through the stored procedures in DNN and didn't see anything obvious. Is anyone familiar with this task and could you steer me in the right direction? Go DNN!

@Mark: You're a veteran DNN-er, so you probably have a great reason to directly access the database.  So, this response is mostly for others that may come across it... :)

It is never a good idea to write queries against or otherwise directly access the database of DNN (or any platform for that matter).  This is because the database is not considered to be part of the API.  As a result, the schema, stored procedures, and all other aspects of the database are subject to change without notice and will almost certainly lead to breaking changes for anyone directly accessing the database.  Instead, the API itself should be used whenever possible to avoid breaking changes in the future. 

(By the way, sadly, this kind of approach is often how some people come to think DNN sucks.  It broke, so it must be DNN's fault.)

There are use cases for direct database access, like using DNN Reports module to present a list instead of creating a custom module. Of course, you need to be aware that schema changes may occurr in new DNN versions and you need to test DNN upgrades thoroughly.