get stored proc

hello

i want to write stored proc.

pls find the attached sample data:

sample output :

table definations:

USE [myapp]
GO

/****** Object:  Table [dbo].[Folder]    Script Date: 10/7/2021 2:35:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Folder](
   [FolderID] [int] IDENTITY(1,1) NOT NULL,
   [FolderName] [varchar](500) NOT NULL,
   [ParentID] [int] NOT NULL,
 CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED 
(
   [FolderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


------
USE [myapp]
GO

/****** Object:  Table [dbo].[Query]    Script Date: 10/7/2021 2:35:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Query](
   [QueryID] [int] IDENTITY(1,1) NOT NULL,
   [QueryName] [varchar](500) NOT NULL,
   [FolderID] [int] NOT NULL,
   [QueryContent] [text] NULL,
   [OwnerName] [varchar](50) NULL,
   [QueryContentIndex] [varchar](50) NULL,
   [StorageClass] [varchar](50) NULL,
   [IsForViewTemplate] [varchar](1) NULL,
 CONSTRAINT [PK_Query] PRIMARY KEY CLUSTERED 
(
   [QueryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Open in new window

note: default root folder is hard coded as "All Searches" with folder id as -1

i want to write stored proc, which returns me to display the data in

parent folder and child etc..

SQLDatabasesMicrosoft SQL Server

Last Comment

Natchiket

8/22/2022 - Mon

Natchiket

10/8/2021

How about something like this

with fh (FolderID,FolderName,ParentID) as
(select FolderID,FolderName,ParentID from Folder where ParentID=0
union all
select s.FolderID,s.FolderName As FolderName,s.ParentID
from Folder s
inner join fh o on o.FolderID=s.ParentID)


Select FolderName,QueryName from

(

select FolderName,QueryName,fh.FolderID from fh

left join Query Q on fh.FolderID=Q.QueryID
union
select Null,QueryName,9999999 as FolderID from Query where FolderID=0) x
order by FolderID

Open in new window

Although it's not a stored procedure, the recursive cte should reflect the folder hierarchy

ram27

10/8/2021

ASKER

Hello Natchiket
as per the data
we have "ramtest22" with parent folder as "ramsearch"?
but not showing in the result

Natchiket

10/8/2021

oops my bad, this is better

WITH fh(FolderID, FolderName, ParentID) 
AS ( SELECT FolderID, FolderName, ParentID FROM Folder WHERE ParentID = 0
  UNION ALL 
  SELECT s.FolderID,s.FolderName, s.ParentID FROM dbo.Folder AS s 
    INNER JOIN fh AS o ON o.FolderID = s.ParentID
    ) 
SELECT 
  FolderName,QueryName 
FROM 
  (
    SELECT fh_1.FolderName, Q.QueryName, fh_1.FolderID 
    FROM 
      fh AS fh_1 
      left JOIN dbo.Query AS Q ON fh_1.FolderID = Q.FolderID 
    UNION 
    SELECT 
      NULL AS Expr1, QueryName, 9999999 AS FolderID FROM Query WHERE FolderID = 0
  ) AS x 
ORDER BY 
  FolderID