Re: Sending NULL value to parameter of Stored Procedure

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

--
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: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: msreplication_monitordata
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... "Hilary Cotter" wrote: ... This seems to only have non null values for the merge agent and then I ...
    (microsoft.public.sqlserver.replication)
  • Re: Peer-to-peer conflict resolution
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.replication)
  • Re: ReIndexing while using replication Important
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... "Hilary Cotter" wrote: ... Does the reindexing naturally replicate to the subscribers? ...
    (microsoft.public.sqlserver.replication)
  • Re: sp_addarticle without re-creating entire new snapshot
    ... "Hilary Cotter" wrote: ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Use Paul ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscriber scheduling
    ... "Hilary Cotter" wrote: ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... I have set up SQL Server 2005 Express as a subscriber. ...
    (microsoft.public.sqlserver.replication)