Using LIKE in stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using LIKE in stored procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-10 : 09:37:24

Steve writes "SQL 2000/Windows 2000

SQL that works:

SELECT *
FROM filename
WHERE fieldname LIKE '%parameter%'

Stored procedure that does not:

CREATE spSearch @parameter char(10) AS
SELECT *
FROM filename
WHERE fieldname LIKE '%@parameter%'

Is there a problem with parameters in a LIKE statement in stored procedures? If I substitute a literal value it works, but the stored procedure does not find matches for the parameter value.

Note: The actual SQL statements are much more complicated than this, but I have narrowed the failure down to this place, and I cannot find an answer in any of my literature."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-05-10 : 09:48:12

have a look under DYNAMIC SQL.

there are many references on this site about this.

Teroman
Posting Yak Master

115 Posts

Posted - 2002-05-10 : 10:00:05

no need for dynamic sql, just a problem with your proc

the way you wrote it its looking for strings with '@parameter' in them, not the value of the parameter

instead try

.....stuff here.....
where fieldname like '%' + rtrim(@parameter) + '%'

col

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-05-10 : 10:54:35

Now why didn't I see that???....

I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!
I must read these posts slower!!!

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-10 : 11:05:36

Curious, but do you need the rtrim? or could it just be

WHERE fieldname LIKE '%(@parameter%)'

?

dsdeming

479 Posts

Posted - 2002-05-10 : 11:07:17

You need the RTRIM because your parameter is defined as char which will retain trailing blanks.

Teroman
Posting Yak Master

115 Posts

Posted - 2002-05-10 : 11:54:54

M.E. - the way you have it does not include a variable, you have a fixed string, one that just happens to have an @ sign in it. I tis still a constant.

you must have the @parameter outside the ' marks

col

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-10 : 12:48:35

ahh I got it

should be '%'+@parameter+'%'

could the parameter itself contain the '%'

I mean so
@parameter = '%ACB%'

and then just like (@parameter)

not sure if that'd work... I think it would.
and thanks for pointing that out Teroman

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-10 : 13:15:24

quote:
could the parameter itself contain the '%'

I mean so
@parameter = '%ACB%'

and then just like (@parameter)

not sure if that'd work... I think it would.

Yep, that would work fine. You may not even need the parentheses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources