SQL Creating a Search Statement Problem

How would I go about combining these two statements. Basically this is a search, I want the user to be able to search for either name, company, or notes. Notes is in another table called act. Which has a foreign key of CID. I would like it to return only results that match whatever has been filled out. So a search for Name: Bob Notes: Golf wouldnt return all the users that had golf in the notes, but would only return someone with a name of bob and has golf in the notes field.

The problem I am running into is what if Notes does return more than 1 row, then it destroys the chance of me using that for the 1st statement.

I'm using SQL-Server 2005. Thanks

1st开发者_如何学运维 Statement:

SELECT DISTINCT CID, Name, Comp, Email
From Con
Where (Name IS NULL OR Name  LIKE '%' + @name + '%')
AND   (Comp IS NULL or Comp LIKE '%' + @comp + '%')

2nd Statement:

If @notes <> ''
Begin
SET @newid = 
(
Select CID    
From Act
Where Note LIKE  '%' + @notes + '%'
)
Select DISTINCT CID, Name, Comp, Email
From Contacts  c
Where (CID= @newid) 
End

---

SELECT DISTINCT CID, Name, Comp, Email
From Con
Where (Name IS NULL OR Name  LIKE '%' + @name + '%')
    AND   (Comp IS NULL or Comp LIKE '%' + @comp + '%')
    AND (
        @notes = ''
        OR EXISTS (
            Select CID    
            From Act
            Where Note LIKE  '%' + @notes + '%'
                AND @notes <> ''
                AND Act.CID = Con.CID
        )
    )

---

SELECT DISTINCT CID, Name, Comp, Email
From Con
Where (Name IS NULL OR Name  LIKE '%' + @name + '%')
AND   (Comp IS NULL or Comp LIKE '%' + @comp + '%')
UNION ALL
Select DISTINCT CID, Name, Comp, Email
From Contacts  c
Where (CID = (Select CID From Act Where @notes <> '' AND Note LIKE  '%' + @notes + '%'))