Passing a wild card field into a stored procedure

[ericpsu32]

Here is my dilemma.

i would like to be able to pass a parameter into a stored procedure through access project and use that parameter as part of a wildcard search.

I can pass this parameter to the procedure, but the parameter is not being recognized as a wildcard.

Here is what I have been trying, but it isn't working because it is seeing the field as %whatever% and not '%whatever%'

FieldName Like '%' + @WildCard + '%'

I can't get it to recognize the quotes. Maybe i am going down teh wrong path entirely. Any help would be appreciated.

Thanks!

 

[SQLDenis]

is this Like '%''' + @WildCard + '''%' what you want?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ericpsu32]

Denis...thanks for the quick reply.

I tried the code you sent and got the following...
%'tech'%

I would like my result to say
'%tech%'

 

[SQLDenis]

switch the quotes

'''%' + @WildCard + '%'''

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ericpsu32]

I got the right result with this:
'''%'+ RTRIM(@WildCard) + '%'''

The problem is that the query still won't pull anything. I ran some simple queries to validate...

Select * from TABLE where FIELDNAME like '%tech%'
this works.

Select * from TABLE where FIELDNAME like @wildcard
This doesn't, even though @wildcard = '%tech%'

 

[bborissov]

What is the value of @wildcard, can it be NULL?

What are the result of:

DECLARE @test varchar(8000)
SET @test = '%' + @wildcard + '%'
print @test

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

 

[SQLDenis]

bothe of these should work

declare @wildcard varchar(50)
select @wildcard ='tech'


Select * from TABLE where FIELDNAME like '%' + @wildcard '%'


declare @wildcard varchar(50)
select @wildcard ='%tech%'


Select * from TABLE where FIELDNAME like @wildcard

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

If you got the result with RTRIM() then @WildCard is not varchar.

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

 

[ericpsu32]

OK... I got it with this. I found an error in another variable I was passing.

Thanks Denis...

Like '%'+ RTRIM(@WildCard) + '%'