Parameterized query nightmares

[Guest]

I'm having a very strange problem with a parameterized query failing. I
grabbed the query in Profiler to see if something weird was going on. Here
it is, nonsense stripped out for brevity:

exec sp_executesql N'SELECT CabinetID, CabinetName FROM Cabinets WHERE
(CabinetName LIKE N''%@SearchText%'') ORDER BY CabinetName', N'@SearchText
nvarchar(16)', @SearchText = N'tech'

If I manullay change the LIKE statement to replace @SearchText directly with
tech (i.e., "LIKE N''%tech%''), the command completes successfully in Query
Analyzer and I get back the data I expect. If I run it as is in QA, I get
back nothing. I've checked my authentication, and that's not the problem (if
I go back to a concatenated select statement in my program it works fine),
I've tried different sizes (and no size at all) for the nvarchar, and that
doesn't change the result. I'm probably missing something very simple, but I
haven't been able to figure it out thorugh research. Help!

 

[Patrice]

This is not a parameter as this is *inside* a literal string... You are just
searching if the column contains the @SearchText text...

Try LIKE '%'+@SearchText+'%' instead so that the parameter is not inside a
literal string.

 

[Miha Markic [MVP C#]]

Enclude percent signs within parameter value:
@SearchText = N'%tech%'
and
(CabinetName LIKE @SearchText')

OK?

 

[Guest]

Thank you for the quick reply. What you suggest doesn't work, but you're on
the ball. I changed it to "(CabinetName LIKE @SearchText)" and it worked as
expected. Merci beaucoup.