Hi,

I can't figure out why my stored procedure is returning so many records. The search works, but it loops through the record thirteen times when binding them to the Gridview. I have removed and inserted the NULL default in the parameter definitions, and have also played with different applications of the parentheses. Does anyone see any glaring errors in this procedure?

 ALTER PROCEDURE dbo.SearchClients
(
    @FirstName nvarchar(50) = NULL,
    @LastName nvarchar(50) = NULL,
    @YearBorn nvarchar(50) = NULL,
    @Phone nvarchar(15) = NULL,
    @AddressOne nvarchar(50) = NULL
)
AS
    SET NOCOUNT ON;
SELECT Clients.ClientID, Clients.FamilyID, Clients.FirstName, Clients.LastName, Clients.YearBorn, Clients.MonthBorn + '/' + Clients.DayBorn + '/' + Clients.YearBorn AS Birthday, Clients.SiteID, Clients.Relationship, Clients.Phone, Clients.FirstName + ' ' + Clients.LastName AS FullName, Clients.Gender, Genders.GenderDescription, Clients.ChapterID, Chapters.ChapterName, Addresses.AddressOne, Addresses.AddressOne + ', ' + Addresses.AddressTwo + '  ' + Addresses.City + ', ' + Addresses.State + ' ' + Addresses.ZipPlusFour AS FullAddress, Sites.SiteName

FROM Clients

INNER JOIN Genders ON Clients.Gender = Genders.GenderID
INNER JOIN Chapters ON Clients.ChapterID = Chapters.ChapterID
INNER JOIN Addresses ON Clients.FamilyID = Addresses.AddressID
INNER JOIN Sites ON Clients.SiteID = Sites.SiteID

WHERE FirstName LIKE @FirstName + '%' AND LastName LIKE @LastName + '%' AND YearBorn LIKE @YearBorn + '%' AND Phone LIKE '%' + @Phone + '%' AND AddressOne LIKE '%' + @AddressOne + '%'

When you do NNER JOIN, you will get some duplicate records. Put DISTINCT like

SELECT DISTINCT Clients.ClientID, Clients.FamilyID......

Hi -- I've tried modifying the stored procedure by inserting the SELECT DISTINCT keywords, and this has the odd effect of duplicating each record thirteen times in a row. Without the DISTINCT keyword, it duplicates the entire set of results thirteen times in a row. Argh!

 I must be misunderstanding the concept of the INNER JOIN. I assumed that this was joining all of the tables into one master table linked by the common ID, and then filtering by the WHERE clause. Can't figure out where I'm going wrong with this...

Thanks!

 

Hi Buddy ,

I think the problem lies with ur Inner Joins.

Please understand the following example

Select a.(attribute1),a.(attribute2),a.(attribute3),b.(attribute1),b.(attribute2),b.(attribute3),c.(attribute1),c.(attribute2),c.(attribute3),d.(attribute1),d.(attribute2),d.(attribute3)

 From (Tablename1) as a Inner Join (TableName2) as b on a.(attribute1)= b.(attribute2) Inner Join (TableName3) as c on (b.attribute1)=(c.attribute1) Inner Join (TableName 4) as d on c.(attribute1)=d.(attribute1) Where (//Specify ur own Condition Here).

I think the error in the code was you defined all Innerjoins at once without specifying the condition particular to a specific inner join

Mark As Answer if this helps you

Further Queries Recommended

Happy Coding 

 Hi -- Thank you so much, ShivaKarthik. Your suggestion caused me to reevaluate the JOIN logic, and this solved the problem. I realized that I was joining the Addresses table with the Clients table on the basis of a common Family ID. However, multiple records can have the same Family ID, and this was causing it to loop wildly. I narrowed down the JOIN condition to filter out everything but the *active* address, and this solved my problem.

Thanks!