-- =============================================
-- Create table function (TF)
-- =============================================
CREATE FUNCTION fn_getAllFolderFileList
(
@i int
)
RETURNS @re TABLE(oID int,IsFolder bit)
AS
BEGIN
DECLARE @d TABLE(FolderID int)
INSERT INTO @d
SELECT @i
WHILE ((SELECT COUNT(*) FROM @d)>0)
BEGIN
SELECT @i=FolderID FROM @d
DELETE FROM @d WHERE FolderID = @i
INSERT INTO @d
SELECT Categories_ID FROM Doc_Categories
WHERE Father = @i
INSERT INTO @re
SELECT FileID,0 FROM File_File WHERE FolderID = @i
INSERT INTO @re
SELECT Categories_ID,1 FROM Doc_Categories WHERE Father = @i
END
RETURN
END
GO