/*
    Technique: Recursive procedure
    Created: 2008-02-21
    Author: Weisenheimer Brainstorm
 
    Based on the standardized textbook classification:
    http://en.wikipedia.org/wiki/Mammal_classification
*/
 
-- The taxonomy table contains a tree structure. 
-- ParentID=NULL indicates the root i.e. the highest taxonomic rank.
 
-- Taxonomic ranks:
-- 1-Class
-- 2-Subclass
-- 3-Infraclass
-- 4-Order
-- 5-Family
-- 6-Subfamily
-- 7-Species
 
IF OBJECT_ID('Taxonomy') IS NOT NULL DROP TABLE Taxonomy
GO
CREATE TABLE Taxonomy (
    ID INT PRIMARY KEY,
    ParentID INT NULL CONSTRAINT FK_NodeId FOREIGN KEY REFERENCES Taxonomy(ID),
    TaxRank INT CHECK (TaxRank BETWEEN 1 AND 7),
    LatinName NVARCHAR(50) NOT NULL,
    EnglishName NVARCHAR(50) NOT NULL
)
GO
 
-- Insert sample data
SET NOCOUNT ON
INSERT INTO Taxonomy VALUES(1,   NULL, 1,    'Mammalia', 'Mammals')
INSERT INTO Taxonomy VALUES(2,   1,    2,    'Prototheria', '')
INSERT INTO Taxonomy VALUES(3,   2,    4,    'Monotremata', 'Egg-laying mammals')
INSERT INTO Taxonomy VALUES(4,   3,    5,    'Tachyglossidae', 'Echidnas')
INSERT INTO Taxonomy VALUES(5,   4,    7,    'Tachyglossus aculeatus', 'Short-beaked echidna')
INSERT INTO Taxonomy VALUES(6,   2,    5,    'Ornithorhynchidae', 'Platypuses')
INSERT INTO Taxonomy VALUES(7,   6,    7,    'Ornithorhynchus anatinus', 'Platypus')
INSERT INTO Taxonomy VALUES(8,   1,    2,    'Theria', '')
INSERT INTO Taxonomy VALUES(9,   8,    3,    'Metatheria', 'Marsupials')
INSERT INTO Taxonomy VALUES(10,  9,    4,    'Didelphimorphia', '')
INSERT INTO Taxonomy VALUES(11,  10,   5,    'Didelphidae', 'Opossums')
INSERT INTO Taxonomy VALUES(12,  11,   7,    'Didelphis virginiana', 'Virginia opossum')
INSERT INTO Taxonomy VALUES(13,  9,    4,    'Paucituberculata', '')
INSERT INTO Taxonomy VALUES(14,  13,   5,    'Caenolestidae', 'Shrew opossums')
INSERT INTO Taxonomy VALUES(15,  14,   7,    'Caenolestes condorensis', 'Andean rat opossum')
SET NOCOUNT OFF
 
-- A helper function to print taxons
IF OBJECT_ID('dbo.fn_FormatTaxon') IS NOT NULL DROP FUNCTION dbo.fn_FormatTaxon
GO
CREATE FUNCTION dbo.fn_FormatTaxon(
    @Level INT,
    @TaxRank INT, 
    @LatinName NVARCHAR(50), 
    @EnglishName NVARCHAR(50)) 
RETURNS NVARCHAR(2000)
AS
BEGIN
    DECLARE @str NVARCHAR(2000), @TaxRankStr VARCHAR(50)
 
    SELECT @TaxRankStr = 
       CASE @TaxRank
           WHEN 1 THEN 'Class'
           WHEN 2 THEN 'Subclass'
           WHEN 3 THEN 'Infraclass'
           WHEN 4 THEN 'Order'
           WHEN 5 THEN 'Family'
           WHEN 6 THEN 'Subfamily'
           WHEN 7 THEN 'Species'
           ELSE ''
       END
 
	SET @str = SPACE(@Level*4) + 
	           @TaxRankStr + ': ' +
		       CASE WHEN @EnglishName<>'' THEN @EnglishName+' ' ELSE '' END + 
		       '(' + @LatinName + ')'
    RETURN @str
END
GO
 
-- ShowClassification shows the taxonomy from the root node down to the given node (@ShowNodeFirst=0)
-- (e.g. Mammals->Marsupials->Opossums->Virginia opossum)
-- or 
-- Shows the taxonomy from the given node up to the root node (@ShowNodeFirst=1)
-- (e.g. Virginia opossum->Opossums->Marsupials->Mammals)
IF OBJECT_ID('ShowClassification') IS NOT NULL DROP PROC ShowClassification
GO
CREATE PROC ShowClassification
    @NodeID INT,
    @ShowNodeFirst BIT = 0,
    @Level INT OUTPUT
AS
    -- Check if the node exists
    IF (SELECT ID FROM Taxonomy WHERE ID=@NodeId) IS NOT NULL
    BEGIN
        DECLARE @TaxRank INT, @ParentID INT, @LatinName NVARCHAR(50), @EnglishName NVARCHAR(50) 
 
        -- Grab info for the given node
        SELECT @ParentID=ParentID, @TaxRank=TaxRank, @LatinName=LatinName, @EnglishName=EnglishName
        FROM Taxonomy WHERE ID = @NodeID
 
        -- Show the taxonomy from the given node up to the root node
        IF @ShowNodeFirst = 1 
        BEGIN
            PRINT dbo.fn_FormatTaxon(@Level, @TaxRank, @LatinName, @EnglishName)
            SET @Level = @Level + 1
        END
 
        -- Call the sproc recursively
        IF @ParentID IS NOT NULL EXEC ShowClassification @ParentID, @ShowNodeFirst, @Level OUTPUT
 
        -- Show the taxonomy from the root node down to the given node
        IF @ShowNodeFirst = 0
        BEGIN
            PRINT dbo.fn_FormatTaxon(@Level, @TaxRank, @LatinName, @EnglishName)
            SET @Level = @Level + 1
        END
    END
    ELSE
    BEGIN
        PRINT 'Node not found'
    END
GO
 
IF OBJECT_ID('ShowTaxon') IS NOT NULL DROP PROC ShowTaxon
GO
CREATE PROC ShowTaxon
    @NodeId INT,
    @ShowNodeFirst BIT = 0
AS
    DECLARE @Level INT
    SET @Level = 0
    EXEC ShowClassification @NodeId, @ShowNodeFirst, @Level OUTPUT
GO

Example #1:

-- 12 - Virginia opossum
-- Class -> Species by default
EXEC ShowTaxon 12

Class: Mammals (Mammalia)
    Subclass: (Theria)
        Infraclass: Marsupials (Metatheria)
            Order: (Didelphimorphia)
                Family: Opossums (Didelphidae)
                    Species: Virginia opossum (Didelphis virginiana)

Example #2:

-- 12 - Virginia opossum
-- 1 - Species -> Class
EXEC ShowTaxon 12, 1

Species: Virginia opossum (Didelphis virginiana)
    Family: Opossums (Didelphidae)
        Order: (Didelphimorphia)
            Infraclass: Marsupials (Metatheria)
                Subclass: (Theria)
                    Class: Mammals (Mammalia)