{\rtf1\ansi\ansicpg1252\deff0\deflang1036{\fonttbl{\f0\fswiss\fcharset0 Arial;}} {\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs20\par CREATE FUNCTION f_toto_recursion1 (@NameId INT, @RefId INT,@ChainLength INT)\par RETURNS @Results TABLE (NameFk1 INT ,RefFk1 INT , RelQualifierFk INT,Sens VARCHAR(15),NameFk2 INT,RefFk2 INT, Cle VARCHAR(200), Rang1 INT, Rang2 INT)\par AS\par \tab BEGIN\par \par \par INSERT INTO @Results \par SELECT PTNameFk1, PTRefFK1 , RelQualifierFk , '',\par PTNameFk2, PTRefFk2 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk\par FROM RelPTaxon,Name N1, Name N2\par WHERE (PTNameFk1 = @NameId AND PTRefFk1 = @RefId AND PTNameFk1 = N1.NameId AND PTNameFk2=N2.NameId)\par \tab AND RelQualifierFk != '-99'\par \par INSERT INTO @Results\par SELECT PTNameFk2, PTRefFK1, RelQualifierFk, '_inverse',\par \tab PTNameFK1, PTRefFk1, CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk\par FROM RelPTaxon,Name N1,Name N2\par WHERE (PTNameFk2 = @NameId AND PTRefFk2 = @RefId AND PTNameFk2=N1.NameId AND PTNameFk2=N2.NameId)\par \tab AND RelQualifierFk != '-99'\par \par \par WHILE (@ChainLength > 0) BEGIN\par INSERT INTO @Results \par \tab SELECT DISTINCT\par \tab\tab RPT.PTNameFk2,RPT.PTRefFK2, RPT.RelQualifierFk, '_inverse',\par \tab\tab RPT.PTNameFk1, RPT.PTRefFk1,CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk\par \tab\par FROM RelPTaxon RPT, @Results R, Name N1, Name N2 \par WHERE \par R.RefFk2 = RPT.PTRefFK2 AND R.NameFk2 = RPT.PTNameFK2 AND RPT.PTNameFk2 = N2.NameId AND RPT.PTNameFk1=N1.NameId\par AND RPT.RelQualifierFk != '-99'-- AND (N1.RankFk > 20 or N2.RankFk > 20)\par AND CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par \tab AND R.Rang2>20\par \tab INSERT INTO @Results \par \tab SELECT DISTINCT\par \tab\tab RPT.PTNameFk1,RPT.PTRefFK1, RPT.RelQualifierFk, '',\par \tab\tab RPT.PTNameFk2, RPT.PTRefFk2,CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk\par \tab\par FROM RelPTaxon RPT, @Results R, Name N1, Name N2 \par WHERE \par R.RefFk2 = RPT.PTRefFK1 AND R.NameFk2 = RPT.PTNameFK1 AND RPT.PTNameFk1 = N1.NameId AND RPT.PTNameFk2 = N2.NameId\par AND RPT.RelQualifierFk != '-99' --AND (N1.RankFk > 20 or N2.RankFk > 20)\par AND CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results)\par \tab\tab AND R.Rang2>20\par SET @ChainLength = @ChainLength - 1\par END\par RETURN\par END\par \par \par \par \par }