sql server - T-SQL - How to write a complex conditional join including a many-to-many join -

I have tried to post another related question to solve this problem which is centered on some parts of the query. Although I can fully post and see if anyone can help. I have the following tablets with the following fields:

tblPerson - PersonID, PersonName
tblGroup - Group ID, Name
tblGroupMembership - PersonID, GroupID
tblPersonCities - CityID, PersonID, City < / P>

This is a very simple setup. We have a person and group and there are many people to join group membership. And then people can have many cities.

Whatever I am trying to do, is writing a stored procedure for exploring this data based on a few different criteria. The collected procedure will only take away the information of the person stored procedure should take 3 parameters:

@PersonName - is the name of a person or a person's name
GroupIDList - A comma delimited list GroupIDs
@City - Name of a city or part of a city

I would not like the value of any parameters for the stored procedure, so if all the parameters were zero then all of them would be Yankee Records should be returned. If the group has been passed in a list of the IDs, then only the person's record should be returned, which corresponds to all groups in the nearest list. I hope I have explained it correctly. I know this is a long question, but I can not explain it in any other way. I have some code which works the most, only one problem is that all these parameters are zero, if it does not seem to work (and I do not know how to get to the city) here my code sample I (note: FNSplit is a custom function that takes a delimited string with commas and returns the table with different values)

  declared @name varchar (50) declared @tc varchar (50) @grouplist Announce varchar (50) @ Name = set @city = null @grouplist = null different p.PersonID, p.PersonName selection, p left tblPerson from c.City p.PersonID = c.PersonID joining tblCities to join c (select null set set From the m.PersonID tblGroupMembership meter (group (count * (*) = (select count (*) by fnSplit (@) (from * mgroupID) (Select item from fnSplit (@grouplist, ','))) m.PersonID Grouplist, ',')))) as filter (do not have the @clubplist faucet) and (P.PIRIDID = Filters.poocidid) where ((name is null) or (P.Personname '%' + @name +% ')) Su ((@ City is empty) or (such as '%' + CT + '%' c. Seat)  
 
 I tend to use dynamic SQL in these situations because     ST O is awesome for performance and removal to accommodate this argument. The following example is targeted for SQL Server 2005 +: 
 
  announcement @SQL NVARCHAR (4000 ) Set @ SQL = 'Select DISTINCT p.personid, p.personname, TBLPERSON p from left c.city Join TBLCITIES c on c.personid = p.personid' set @sQL = @sQL + is the case when @grouplist Is not zero 'JOIN (TBLGROUPMEMBERSHIP Select m.PersonID from Itr where the m.GroupID ((by selecting items from fnSplit @grouplist, ','))) GroupM. PSCNID Hewitt COUNT (*) = (From FANSSplit (@GroupList, ',')) Select ()) G.G. Pericinid = PPSride 'ELSE' and SET = SQL = @ SQL + 'WHERE 1 = 1' - To make the contatentating WHERE clause easier for the trick, if IF is not an IF @name, then zero set @ sql = @ sql + PPSName '%' + @Name + '%' If @city zero set @SQL = @SQL + 'and' c'icity '%' + @city + '%' starts EXEC sp_executesql @SQL n '@ grouplist varchar ( 50), @grouplist is not varchar (50), @name varchar (50), @city varchar (50) ', @grouplist, @grouplist, @name, @city end  
 
 The mind will be sp_executesql   the query plan of the cache - per.