> 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 [at] SearchCriteria = 2
> BEGIN
> declare [at] SearchTerm varchar(8000)
> set [at] 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 [at] 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 [at] 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 [at] 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
> [at] 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
