
PRINT 'EXECUTE 028'

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_mclBook_getRealBasionym')
	DROP FUNCTION dbo.f_mclBook_getRealBasionym
GO

CREATE FUNCTION dbo.f_mclBook_getRealBasionym (@synonymNameId int) 
RETURNS int
AS
BEGIN
	-- get the basionym for the given taxon
	DECLARE @basionymNameId AS int
	SET @basionymNameId = (
		SELECT     dbo.RelName.NameFk1
		FROM         dbo.RelName
		WHERE     (dbo.RelName.RelNameQualifierFk = 1) AND (dbo.RelName.NameFk2 = @synonymNameId)
	)	

	-- check if the basionym is a basionym for an accepted taxon
	IF ((SELECT     COUNT(*) AS Expr1
			FROM         dbo.RelName INNER JOIN
                      dbo.PTaxon ON dbo.RelName.NameFk2 = dbo.PTaxon.PTNameFk
			WHERE     (dbo.PTaxon.StatusFk = 1) AND (dbo.RelName.NameFk1 = @basionymNameId)) > 0)
	BEGIN
		RETURN @basionymNameId
	END	

	-- basionym for synonym only, carry on
	DECLARE @realBasionymNameId int

	SET @realBasionymNameId = (
		SELECT     dbo.RelName.NameFk1
		FROM         dbo.RelName
		WHERE     (dbo.RelName.RelNameQualifierFk = 3) AND (dbo.RelName.NameFk2 = @basionymNameId)
	)

	RETURN @realBasionymNameId
END 
GO


