如何在SQL Server 2008中编写基于姓名、企业名称、大学名称的搜索存储过程

Question

我想搜索一个基于姓名，企业名称，大学名称，技能名称的表。

假设我将按名称搜索一次，按企业名称搜索一次，按大学名称搜索一次，但是当我输入名称时，我单击搜索按钮，我将显示正确。但是当我输入businessname或universityname时，我没有得到正确的结果，我会得到businessname列全部为空，如null

我写了一个这样的存储过程:

ALTER PROCEDURE [dbo].[SearchPeople_test]
(
      @FirstName nvarchar(255),
      @Location nvarchar(100),
      @MembershipUserID uniqueidentifier,
      @UniversityName nvarchar(255),
      @Postcode nvarchar(20),
      @BusinessName nvarchar(255),
      @AssociateMemberID uniqueidentifier,
      @SkillName nvarchar(255),
      @Debits int,
      @Gender int
  )
AS
BEGIN
    SET @SkillName = [dbo].[fnGetMemberskills](@AssociateMemberID)
    SET @Debits = [dbo].[fnGetMembersSpentCredits](@AssociateMemberID)
    SET NOCOUNT ON;
SELECT  DISTINCT  AssociateMember.ID AS ID       
        ,AssociateMember.Firstname AS FirstName        
        ,AssociateMember.Lastname AS LastName
        ,Address.Location AS Location
        ,Address.City AS City
        ,BusinessProfile.BusinessName
        ,EducationHistory.UniversityName
        ,AssociateMember.MembershipUserID AS MembershipUserID
        ,EmploymentHistory.JobTitle AS JobTitle
        ,AssociateMember.MemberPhotoUrl AS MemberPhotoUrl
        ,aspnet_Membership.Email AS Email
        ,AssociateMemberJourneyContent.JourneyContent AS JourneyContent
        ,([dbo].[fnGetMemberskills](AssociateMember.ID))  AS MemberSkillList
        ,([dbo].[fnGetMembersSpentCredits](AssociateMember.ID))  AS Debits              
FROM 
    AssociateMember 
INNER JOIN
    aspnet_Membership 
    ON AssociateMember.MembershipUserID=aspnet_Membership.UserId 
left outer join  MemberAddressLink      
    ON AssociateMember.ID=MemberAddressLink.MemberID
left outer join
    Address 
    ON Address.ID=MemberAddressLink.AddressID 
left outer join 
    EmploymentHistory 
    ON AssociateMember.ID=EmploymentHistory.MemberID     
left outer join   
    EducationHistory 
    ON EducationHistory.MemberID=AssociateMember.ID 
left outer join  
    MemberEmploymentHistory 
    ON AssociateMember.ID=MemberEmploymentHistory.MemberID
left outer join 
    BusinessProfile 
    ON BusinessProfile.ID=MemberEmploymentHistory.BusinessProfileID 
left outer join
    MemberSkill 
    ON MemberSkill.MemberID=AssociateMember.ID
left outer join 
    SkillType 
    ON SkillType.ID = MemberSkill.SkillID AND MemberSkill.MemberID=AssociateMember.ID
left outer join
    AssociateMemberJourneyContent
    ON AssociateMember.ID=AssociateMemberJourneyContent.AssociateMemberID
where
((Address.Location)=ISNULL(@Location,Address.Location) and (MemberAddressLink.IsDefault=1)
                 OR (Address.Location like '%'+ @Location+'%')or (Address.Location is null) ) and
 (AssociateMemberJourneyContent.JourneyContentTypeID=1 OR  AssociateMemberJourneyContent.JourneyContentTypeID IS NULL) AND

(((BusinessProfile.BusinessName)=ISNULL(@BusinessName,BusinessProfile.BusinessName) 
                             OR (BusinessProfile.BusinessName like '%'+@BusinessName+'%')or(BusinessProfile.BusinessName is null))
                              AND
                             ((MemberEmploymentHistory.StartDate)=(SELECT  MAX(StartDate) FROM MemberEmploymentHistory 
WHERE MemberEmploymentHistory.MemberID=AssociateMember.ID) OR (MemberEmploymentHistory.StartDate IS NULL))) AND

((AssociateMember.Firstname)=ISNULL(@FirstName,AssociateMember.Firstname) 
                          OR (AssociateMember.Firstname like '%'+@FirstName+'%')OR (AssociateMember.Firstname IS NULL)) AND 

((Address.Postcode)=ISNULL(@Postcode,Address.Postcode) 
                 OR (Address.Postcode like '%'+@Postcode+'%') OR (Address.Postcode IS NULL)) AND
(((EducationHistory.UniversityName)=ISNULL(@UniversityName,EducationHistory.UniversityName) 
                OR (EducationHistory.UniversityName like '%'+@UniversityName+'%') OR (EducationHistory.UniversityName IS NULL)) AND
            ((EducationHistory.StartDate)=(SELECT  MAX(StartDate) FROM EducationHistory 
WHERE EducationHistory.MemberID=AssociateMember.ID) OR (EducationHistory.StartDate IS NULL) ))AND

((AssociateMember.GenderID)=ISNULL(AssociateMember.GenderID,@Gender) 
                          OR (AssociateMember.GenderID IS NULL)) AND

((EmploymentHistory.StartDate)=(SELECT MAX(StartDate) FROM EmploymentHistory 
WHERE EmploymentHistory.MemberID=AssociateMember.ID)OR (EmploymentHistory.StartDate IS NULL)   ) 
end

Answer 1

与其尝试一次完成所有操作，不如将其分解，首先编写针对某列的查询，然后运行它。然后为下一个做，运行它。等等，然后最后合并两个查询，运行，合并3个查询运行等等。如果在任何时候你没有得到你所期望的，分析你在做什么。