If I was to do a select statement from the [Warrants] table below off of intMasterAcctID I would get two rows returned for intMasterAcctID=1

The function basically returns the intMasterAcctID, Warrants and Warrant Percents

So:

Select * from WarrantCodesByAcct(2) returns

2,'PP',100

select * from WarrantCodesByAcct(1) (with 2 qualifying records)

1,'PP,FA','50,50'

Basic table layout w/data

warrants

intID,intMasterAcctID,intWarrantCodeID,intWarrantPerc1

1,1,1,50

2,2,1,100

3,3,1,100

4,1,2,50

WarrantCodes

intID,vchrCode,vchrDescription

1,'PP','Personal Property'

2,'FA','Farm Animals'

MasterRecord

intMasterAcctID,vchrOwner

1,'Chris'

2,'Bob'

3,'Fred'

Foreign key reference between Warrants.intWarrantCodeID=WarrantCodes.intID

Warrants.intMasterAcctID==MasterRecord.intMasterAcctID

CREATE FUNCTION dbo.WarrantCodesByAcct (@intMasterAcctID int)

RETURNS @resultTable

 TABLE (intMasterAcctId  int  primary key,

     Warrants char (50) null,

     Warrants_Perc char(50) null)

AS

BEGIN

--declare temp tables

   DECLARE @Warrants_temp

   TABLE ( intMasterAcctID_  int  ,

        intID_ int,

        Warrant char(50) null,

 WP char(50) null)

   DECLARE @MasterRecord_Temp

   TABLE ( intMasterAcctID  int  primary key,

        Warrants char (50) null,

        _lastintID char(50) null,

 Warrants_Perc char(50) null,

 _lastWP char(50) null)

--copy all Warrants - relation table to temp [we can delete]

If exists(Select intMasterAcctID from Warrants where intMasterAcctID=@intMasterAcctID)

Begin

INSERT INTO @Warrants_temp

SELECT intMasterAcctID, Warrants.intID, vchrCode, intWarrantPerc1

FROM WarrantCodes join Warrants ON (WarrantCodes.intID=Warrants.intWarrantCodeID)

where intMasterAcctID=@intMasterAcctID

--copy all MasterRecords with no owner information

INSERT INTO @MasterRecord_Temp

SELECT intMasterAcctID, '','' ,'','' FROM MasterRecord where intMasterAcctID=@intMasterAcctID

--repeat if needed

WHILE (SELECT count(*) FROM @Warrants_temp)>0

BEGIN

     --update Masterrecord info

     UPDATE @MasterRecord_Temp

     SET Warrants=rtrim(Warrants)+rtrim(Warrant)+',',

         _lastintID=intID_,

       Warrants_Perc=rtrim(Warrants_Perc)+rtrim(WP)+','

     FROM @Warrants_temp

     WHERE (intMasterAcctID=intMasterAcctID_ )

    

     --delete already inserted Warrant info

     DELETE @Warrants_temp FROM

     @MasterRecord_Temp WHERE intID_=_lastintID AND intMasterAcctID=intMasterAcctID_

END --repeat if any Warrant records exists

--copy all records to result table without last comma

INSERT INTO @resultTable

SELECT intMasterAcctID, left(Warrants,len(Warrants)-1), Left(Warrants_Perc,len(Warrants_Perc)-1)  FROM @MasterRecord_Temp

end

RETURN

END