/* 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)