if exists(select name from sysobjects
            where name = 'tblToolsStringParserCounter')
drop table tblToolsStringParserCounter

go
create table dbo.tblToolsStringParserCounter
(
    ID INT
)
declare @i INT
select    @i = 1

while (@i <= 200)
begin
    insert into tblToolsStringParserCounter select @i
    select @i = @i + 1
end


if exists(select name from sysobjects
             where name = 'sp_thes_selConceptByRelation' and type = 'P')
    drop procedure sp_thes_selConceptByRelation
go

create procedure dbo.sp_thes_selConceptByRelation
    @conceptReferenceId int,
    @conceptNameId int,
    @relationIds varchar(100)
as

    create table #1
    (
        IDList Int
    )
    -- Optional index on the temp table
    create index idx1 on #1 (IDList)

    insert into #1
    select  Convert(Int, NullIf(SubString(',' + @relationIds + ',' , ID ,
     CharIndex(',' , ',' + @relationIds + ',' , ID) - ID) , '')) as IDList 
    from    tblToolsStringParserCounter
    where   ID <= Len(',' + @relationIds + ',') and SubString(',' + 
      @relationIds + ',' , ID - 1, 1) = ',' 
    and     CharIndex(',' , ',' + @relationIds + ',' , ID) - ID > 0  

    select  Name.PTNameFk1, Name.NameCache, Name.PTRefFk1, (select Title from Reference where RefId =  Name.PTRefFk1) as ReferenceName1, 
        Name.RelQualifierFk,
        (select RelPTQualifier from RelPTQualifier where RelPTQualifierId = Name.RelQualifierFk) as RelPtQualifier, 
        Name.PTNameFk2, relName.NameCache, Name.PTRefFk2, (select Title from Reference where RefId =  Name.PTRefFk2) as ReferenceName2
        from(
            -- add the name off the related concept to the result
            select Name.NameCache, RelPTaxon.PTRefFk1, RelPTaxon.PTNameFk1, RelPTaxon.RelQualifierFk, RelPTaxon.PTNameFk2, RelPTaxon.PTRefFk2 
                from(
                    -- get all records with the given reference and the desired relation type
                    select PTNameFk1, PTRefFk1, RelQualifierFk, PTNameFk2, PTRefFk2 
                        from RelPTaxon, #1
                    where PTRefFk1 = @conceptReferenceId and #1.IDList = RelQualifierFk
                   )
            as RelPTaxon inner join Name on RelPTaxon.PTNameFk1 = Name.NameId 
            )
         as Name inner join Name as relName on Name.PTNameFk2 = relName.NameId
    where relName.NameId = @conceptNameId
go