Re: Sending NULL value to parameter of Stored Procedure

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: Sending NULL value to parameter of Stored Procedure
    ... You can do an existence check in your header, ... having no value here will have the proc complain when nothing is entered. ... Director of Text Mining and Database Strategy ... Syntax error in search condition, or empty or null search condition ''. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Invalid Descriptor Index
    ... 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: Replication from SQL Server 2005 to SQL Express using RMO
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Const DB_AUTHENTICATION = 0 ... RMO is just like DMO, but only contains the replication components. ...
    (microsoft.public.sqlserver.replication)
  • Re: Backup failure due to full-text indexing
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Shift the backup log after backup database (it was starting at the same ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Fulltext and cluster - odd problem
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... [Location of errorlog files] ...
    (microsoft.public.sqlserver.fulltext)
Loading