Re: Sending NULL value to parameter of Stored Procedure

Oops sorry Simon, I think I missed your point. However until I look at what
he is trying to do, I can't tell if it can't be done without using 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: Log Parser - Telephone call log analysis
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ...
    (microsoft.public.sqlserver.tools)
  • Re: Interactive Resolve with SQL Express
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge Replication Error: "The Process could not enumerate changes at the Subscriber"
    ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... It is SP4 which actually resolves this issue. ... Are there any other issue with installing SP4 wrt Merge ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge Replication Error: "The Process could not enumerate changes at the Subscriber"
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ...
    (microsoft.public.sqlserver.replication)
  • Re: delete a replicated database as publisher
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ...
    (microsoft.public.sqlserver.replication)