September 9, 2003 at 5:53 am
Hi,
I have the following 2 tables; The first stores documents as BLOB's the second allows a relationship between documents to be recorded. For example if document1 is a word mailmerge document and document2 is a spreadsheet with the mailmerge data in. A document can be dependent upon more than 1 other documents.
In the TBL_DocumentLink table the values of DocumentID and SubDocumentID are the values of the
TBL_Document.DocumentID of the master and sub-document.
What I need to be able to do is to create a stored proc where I can pass a documentID and return all documents that are dependent upon that document or one of it's dependent documents. I am at a bit of a loss how to do this recursive checking in SQL. does anyone know how this could be done?
Thanks In advance.
Cheers
Chris
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_Document]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_Document]
GO
CREATE TABLE [dbo].[TBL_Document] (
[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
[DocDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocLongDesc] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocTypeID] [int] NULL ,
[BIN] [image] NULL ,
[LoadPath] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_DocumentLink]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_DocumentLink]
GO
CREATE TABLE [dbo].[TBL_DocumentLink] (
[DocumentLinkID] [int] IDENTITY (1, 1) NOT NULL ,
[DocumentID] [int] NULL ,
[SubDocumentID] [int] NULL
) ON [PRIMARY]
GO
September 9, 2003 at 7:16 am
Rough solution (not tested)
DECLARE @RecCount int
SET @RecCount = 1
CREATE TABLE #TempDoc (DocumentID int)
CREATE TABLE #TempDoc1 (DocumentID int)
CREATE TABLE #TempDoc2 (DocumentID int)
INSERT INTO #TempDoc1 VALUES (@DocumentID)
WHILE (@RecCount > 0)
BEGIN
DELETE FROM #TempDoc2
INSERT INTO #TempDoc2
SELECT d.SubDocumentID FROM #TempDoc1 t
INNER JOIN TBL_DocumentLink d
ON d.DocumentID = t.DocumentID
INSERT INTO #TempDoc SELECT DocumentID FROM #TempDoc2
DELETE FROM #TempDoc1
INSERT INTO #TempDoc1 SELECT DocumentID FROM #TempDoc2
SELECT @RecCount = COUNT(*) FROM #TempDoc1
END
SELECT d.DocumentID,d.DocDesc,d.DocLongDesc,d.DocTypeID,d.BIN,d.LoadPath
FROM TBL_Document d
INNER JOIN #TempDoc t ON t.DocumentID = d.DocumentID
DROP TABLE #TempDoc
DROP TABLE #TempDoc1
DROP TABLE #TempDoc2
Far away is close at hand in the images of elsewhere.
Anon.
September 9, 2003 at 7:57 am
Thanks David,
This looks great! I'll give it a try.
Cheers
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply