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)

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.

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?

Hi Paparush,

I believe the problem is because you're mixing up dynamic SQL with
static SQL.
The @SearchTerm variable that you are building up can not be used to
specify additional LIKE criteria for the Subject column, each LIKE
condition needs to be individually coded into your static SQL.

The alternative is to build the entire SELECT as dynamic SQL. This
provides additional flexibility, but there are performance and
security implications when doing this (google SQL injection).

Good luck!
J

On Dec 17, 10:33 am, jhofm...@googlemail.com wrote:
> Hi Paparush,
>
> I believe the problem is because you're mixing up dynamic SQL with
> static SQL.
> The @SearchTerm variable that you are building up can not be used to
> specify additional LIKE criteria for the Subject column, each LIKE
> condition needs to be individually coded into your static SQL.
>
> The alternative is to build the entire SELECT as dynamic SQL. This
> provides additional flexibility, but there are performance and
> security implications when doing this (google SQL injection).
>
> Good luck!
> J

Hi J,
Thanks for the reply.

Yes..the dynamic SQL is the approach I've been trying all morning and
I've gotten it to work. I have to build the entire SELECT string in
my app's code, based on the search term the user enters and then stuff
this into @SearchTerm and pass this string into the code below.

declare @SearchTerm nvarchar(max)

EXEC sp_ExecuteSQL @Search

> 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

>> So basically, the user might submit one term, or multiple terms. <<

I would assume that you allow only a limited number of terms in one
procedure call. Otherwise someone is going to load in a dictionary
and choke the system.

SELECT * -- never use * in production code
FROM CM_Cases -- more than one?
WHERE case_subject LIKE @search_term_01
OR case_subject LIKE COALESCE (@search_term_02, case_subject )
..
OR case_subject LIKE COALESCE (@search_term_10, case_subject );

This can be compiled and optimized, avoids injection problems, will
port easily and gives you more control over what the user is doing.

> This can be compiled and optimized, avoids injection problems, will
> port easily and gives you more control over what the user is doing.

Compiled - true, although parameter sniffing is a big problem
Avoids injection - true
Port easily - true
More control over the user - don't see how

optimised - 0/10; false; wrong.

Please stop guessing; please install SQL Server and actually try some of
these answers you post.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

Have you tried Full Text, it won't do %foo% but will do the context stuff
you need.

Parameterised dynamic SQL is the way forward otherwise.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

>> More control over the user - don't see how <<

The user can be limited to how many search terms he can input at one
time. If you use dynamic SQL, they can go wild and strangle the
database with hundreds or thousands of requests. It is also easier
to apply edits to the @search_term_# parameters, such as trimming,
upper or lower casing, replacing characters, etc.

> The user can be limited to how many search terms he can input at one
> time. If you use dynamic SQL, they can go wild and strangle the
> database with hundreds or thousands of requests. It is also easier
> to apply edits to the @search_term_# parameters, such as trimming,
> upper or lower casing, replacing characters, etc.
>

Nope - wrong again.

Using a derivative of the CSV approach to passing in the list of search
criteria for that specific column you can do a) all the triming at once, b)
all the validation at once and c) protect yourself from injection.

You can still limit the number of requests, except it's easier and doesn't
require lot's of hard coding and re-testing when you add another parameter.

Did you not do this in your training? This is fundemental - basic stuff.

I noticed you didn't comment on 'optimisation' - I think you fully realise
the problem there (or perhaps you don't because you've never actually done
real coding on real data volumes)??

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]