Wildcard * use in where clause

[Kflasph]

I am trying to create a stored procedure to look for information in specific fields. I am trying to do this by creating a parameter for each field.
Parameters passed:
@strField1 as varchar(20)
@strField2 as varchar (20)
@strField3 as varchar (20)

Values that can be passed:
--For testing purposes
set @strField1='%'
set @strField2='%'
set @strField3='String I'm looking for%'

Any one of the fields can be populated with information at any time.


My select statement looks like:
Select * from MyTable where
Field1 = @strField1 and
Field2 = @strField2 and
Field3 = @strField3

I am not getting all the data back with the select statement. I get only partial results. Is this because you cannot use a wildcard by itself?

Thanks in advance,
Kflasph

 

[Kflasph]

Sorry - the select statement should be

My select statement looks like:
Select * from MyTable where
Field1 like @strField1 and
Field2 like @strField2 and
Field3 like @strField3

 

[AlexCuse]

You need to set up your @strFields to include single quotes around them.

Like this:

set @strField3 = '''String I'm looking for%'''

Without the quotes your query will assume it is a column name.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Kflasph]

I tried the additional quotes and no luck.

set @strField3 = ''ABCDE%''

It did not return anything.

Kflasph

 

[bborissov]

set @strField3='String I''m looking for%'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

set @strField3 = '''ABCDE%'''

You need to use 2 apostrophe's within your string to represent the single quote character.

A wise man once said
"The only thing normal about database guys is their tables".

 

[Kflasph]

Thanks for both replies. Sometimes the obvious has to be spelled out.

Kflasph

 

[AlexCuse]

HOpe it works for you. Have a good weekend!

Alex

A wise man once said
"The only thing normal about database guys is their tables".