Stored Proc with variables and wildcards

[JefB]

In SQL2000 I have the following Stored Procedure:

CREATE PROCEDURE [dbo].[DrugLookup]

@hint varchar

AS

SELECT StudyDrug, StudyDrug2, DrugUsage
FROM dbo.DrugName
WHERE DrugUsage like @hint
GO

The [DrugUsage] field has key words describing the various uses of a particular drug as well as alternate names.

How can I set up the SP to use wildcards (“%”) so that @hint can be fed as just a single word (or portion) and return all the records in which that keyword is found?

I have tried:
DrugUsage like “%” & @hint & “%”
DrugUsage like ‘“%” & @hint & “%”’
DrugUsage like ‘“%”’ & @hint & ‘“%”’

as well as several other variations on that theme. Either nothing is returned or ALL the records are returned.

If there is a better way to do this, I am open to suggestion.

JefB

 

[tlbroadbent]

I have two recommendation. Declare the thength of @hint and use single quotes around the wild card character '%'.

CREATE PROCEDURE [dbo].[DrugLookup]
@hint varchar(30)
AS

SELECT StudyDrug, StudyDrug2, DrugUsage
FROM dbo.DrugName
WHERE DrugUsage like '%' + @hint + '%'
GO

You can also use the charindex function which may prove easier.

SELECT StudyDrug, StudyDrug2, DrugUsage
FROM dbo.DrugName
WHERE charindex(@hint, DrugUsage)>0 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JefB]

The second option worked perfectly!

Thanks

Jef Block