Re: Sending NULL value to parameter of Stored Procedure

Hi Simon, thanks for the links. Erland's arguments are precisely why I am
trying to encourage the OP NOT to use dynamic sql.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"Simon Sabin" <SimonSabin@xxxxxxxxxxxxxxx> wrote in message
news:62959f1a2c6ba8c8d0e58ad6371c@xxxxxxxxxxxxxxxxxxxxxxx
Hello Hilary,

With that number of options dynamic SQL is the best option, you need to
understand the implications, SQL injection, multiple plans. Be careful
about plans based on one set of parameters that may not perform well with
another set.

Have a read of Erlands Dynamic sql article
http://www.sommarskog.se/dynamic_sql.html



Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


I would avoid dynamic queries as much as possible. Can you post your
proc here, or send it to me offline?

This posting is my own and doesn't necessarily represent
RelevantNoise's positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Siva" <Siva@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2C1EF77D-1AD6-4A70-A053-9456C08CD2AC@xxxxxxxxxxxxxxxx

Hi Hilary,

Thanks for your response. I would like to confirm with you one more
thing.

My SP is having around 25 parameters apart from Full Text Search
parameter, I would like to execute the SP for remaining parameters
and
gets
the result.
I would like to exclude only Contains Clause, If the parameter value
is NULL / Empty.

Should i use Dynamic query to achieve the above points?

Thanks,
Siva
"Hilary Cotter" wrote:

You can do an existence check in your header, ie

Create searchproc (@search varchar(200))
as
....
having no value here will have the proc complain when nothing is
entered.
If
you do some triming of the value of @search in your proc, you could
do an
existence check before firing it;
Create searchproc (@search varchar(200))
as
select @search=Replace(@search, char(34)+char(34), char(34))
if len(replace(@search,char(34),'')) > 0
select * from mytable where contains(*, @search)
else
return -1
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business
intelligence.
This posting is my own and doesn't necessarily represent
RelevantNoise's positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Siva" <Siva@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A987EBE5-F578-420E-9A75-220B280B01E1@xxxxxxxxxxxxxxxx

I am getting the following error, when i am trying to pass the NULL
value
to
the parameter of Stored procedure. That parameter has been used in
Contains
clause in T-SQL.
Server: Msg 7603, Level 15, State 1, Procedure spCMXLSearchReports,
Line
256
Syntax error in search condition, or empty or null search condition
''.
This one is working fine, when i am sending any value to the
parameter, which is mentioned Contains caluse.

It's not accepting any null values. Should i use Dynamic query?
Can
you
please help me.




.

Relevant Pages

  • Re: Full text Pattern Search
    ... Basically SQL FTS does a really good job and offers better performance than ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Sending NULL value to parameter of Stored Procedure
    ... Your proc consists of creation of a temp table, ... Looking for a SQL Server replication book? ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Help me convince the dev manager. Please.
    ... SQL - not just in the fact that it's not precompiled, ... > dev managers insists that there must be another method out there to handle ... firmly believe that dynamic SQL is the best ... > to handle optional parameters. ...
    (microsoft.public.sqlserver.programming)
  • Re: the agent is suspect. No response within last 60 minutes
    ... "Hilary Cotter" wrote: ... > IIRC - with concurrent it holds brief or shared locks on the objects it is ... > Looking for a book on SQL Server replication? ...
    (microsoft.public.sqlserver.replication)
  • Re: Help me convince the dev manager. Please.
    ... SQL Server MVP ... firmly believe that dynamic SQL is the best ... >> to handle optional parameters. ... >> ensure that the SQL string is formed correctly. ...
    (microsoft.public.sqlserver.programming)