Re: 2005 Stored Procedure Question - How to pass in additional LIKE conditions

> I'm relatively new to calling stored procedures, and I have a question
> about passing in a parameter.
>
> Very simple search proc that is called to search terms submitted by
> the user. I've just copied the relevant portion here..
>
>
> IF @SearchCriteria = 2
> BEGIN
> declare @SearchTerm varchar(8000)
> set @SearchTerm = char(39)+'%foo%'+ char(39) + ' AND SUBJECT LIKE ' +
> char(39) + '%bar%'+char(39)

Char(39)? That means you're adding additional ' to the string value.

This is not a string literal, it's a placeholder, don't add these, I think.

> SELECT * FROM CM_Case WHERE Subject LIKE @SearchTerm ORDER BY CaseNo
>
> END;
>
> So basically, the user might submit one term, or multiple terms. When
> they submit mulitple terms, my code builds the @SearchTerm as
> described above. However, this code always returns zero results.

Are you concatenating these user strings? Cause that's an excellent way
to get some SQL injection :-)

> If I copy out the @SearchTerm string and run it through Query
> Analyzer, it runs fine and returns a result set.
>
> What is the Stored Proc doing behind the scenes that makes this simple
> query fail when the search clause is passed into the proc via the
> @SearchTerm parameter?


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com