SQL Server recursive SELECT

Not anything to do with WPF at all, but a useful technique to know.

I have a purchasing system. Whenever a user wants to make a change to a purchase order, they have to create a change order so it can go through the appropriate authorization process. The change order is a different document. I need to take the ID of a change order and find it, all prior change orders, and the original purchase order. Each change order contains the ID of the prior purchase order/change order.

We can do this with Common Table Expressions (CTE).

Start by creating a new table

CREATE TABLE [dbo].[DocumentTable](

       [ID] [int] IDENTITY(1,1) NOT NULL,

       [DocumentType] [varchar](50) NOT NULL,

       [PriorDocumentID] [int] NULL,

       [DocumentName] [varchar](50) NOT NULL

) ON [PRIMARY]

GO

and populate it

DECLARE @ID int

INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('PO',NULL,'Purchase Order'); SELECT @ID=SCOPE_IDENTITY()

INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 1'); SELECT @ID=SCOPE_IDENTITY()

INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 2'); SELECT @ID=SCOPE_IDENTITY()

INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 3'); SELECT @ID=SCOPE_IDENTITY()

INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 4'); SELECT @ID=SCOPE_IDENTITY()

INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 5'); SELECT @ID=SCOPE_IDENTITY()

Now given an ID I want to return the document with that ID and all its ancestors.

DECLARE @ID int

SET @ID=4;

WITH DocumentCTE AS

(

       SELECT * FROM DocumentTable WHERE ID=@ID

       UNION ALL

       SELECT DT.* FROM DocumentTable DT INNER JOIN DocumentCTE CTE ON DT.ID=CTE.PriorDocumentID

)

SELECT * FROM DocumentCTE

With a starting ID of 4 we see the following results. As you can see, the CTE has recursed up the chain until it found a document with no PriorDocumentID.

ID          DocumentType    PriorDocumentID DocumentName

----------- ------------    --------------- --------------

4           CO              3               Change Order 3

3           CO              2               Change Order 2

2           CO              1               Change Order 1

1           PO              NULL            Purchase Order

What happens if the recursion is infinite - for example, if we set the PriorDocumentID of the PO to 6? The algorithm recurses round and round until the maximum recursion limit (default 100) is hit. Then an error is thrown.

Msg 530, Level 16, State 1, Line 3

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.