It is not nice, but it can be fast.

IF @ContactType = 'Broker'

Begin

SELECT DISTINCT

c.ContactID,

c.ContactName,

c.PhoneNum,

c.Fax,

c.PersonalEmailAddress,

c.CompanyPhoneNum,

c.GroupEmailAddress,

c.Address1,

c.Address2,

c.Address3,

c.Address4,

c.City,

c.State,

c.Zip,

c.CountryCode

FROM

tblDCF_SubAccounts s inner join

tblDCF_Contacts_Accounts a on s.HiNetMinor = a.HiNetMinor inner join

tblDCF_Contacts c on a.ContactID = c.ContactID

WHERE

a.ContactType = @ContactType

AND a.ContactSubType = @ContactSubType

AND s.BrokerID = @ID

Order By c.ContactName

END

ELSE IF @ContactType = 'FundAcct'

Begin

SELECT DISTINCT

/* the same query */

AND s.FundAccountantID = @ID

END

ELSE IF @ContactType = 'Custodian'

Begin

SELECT DISTINCT

/* the same query */

AND s.CustodianID = @ID

END