Using Like with Parameters 1

[ceyhorn]

I am trying to create a search for a grantee, currently i am just passing it in as a parameter, but the drawback is that it has to be an exact match. How can I use the LIKE in my WHERE clause so that it looks for the words in the database?

SELECT ApplicationID, GrantCountID, GranteeName, ProjectTitle, FundingBlock, StartDate, EndDate, FundingSourceID, PlanningYear FROM dbo.tblApp WHERE (@GranteeName = GranteeName)

Thanks in advance,
Chris

 

[Lothario]

then replace it with...

WHERE @GranteeName LIKE 'GranteeName'

-L

 

[ceyhorn]

What about using the % in front and behind the text search?

 

[Lothario]

yes...try this..

WHERE @GranteeName LIKE '%GranteeName%'

it means containing the GranteeName

else

WHERE @GranteeName LIKE '%GranteeName'

it means ending with GranteeName

Else

WHERE @GranteeName LIKE 'GranteeName%'

it means starting with GranteeName

-L

 

[SQLSister]

Beware of using like with a wildcard as the first character. This usage means that the indexes cannot be used and thus your query will become extremely slow as the number of records grow. Best to insist the users have to give you the first part of the name.

If you cannot, then I would suggest that you use if statements to speed the query for when the user gave you the exact match. Then you can search for the exact match and only if no records are returned, use the slower query.

Questions about posting. See faq183-874

 

[ESquared]

Correct SQL would be something like:

WHERE
   GranteeName LIKE GranteeName + '%'

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)