Re: Sending NULL value to parameter of Stored Procedure

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.





.

Loading