[Solved] ‘Null or empty full-text predicate’ when executing a store procedure from a bound control
I’ve got a page in my ASP.Net application that is used to search a database via a store procedure. One of the parameters passed to the stored produced is bound to a text box via a ControlParameter binding.
<asp:ControlParameter Name="EventDescription" Type="String" ControlID="ucTestLogSearch" PropertyName="EventDescription" />
The text is passed to a SQL Server stored procedure.
The stored procedure defines the parameter like this:
@event_descrip VARCHAR(200) = NULL,
And it uses the parameter in a WHERE condition like this:
(CONTAINS (le.event_descrip, @event_descrip) OR @event_descrip IS NULL)
My problem is that if the text box is blank, I get the following exception: ‘Null or empty full-text predicate’.
In my experience, having the check for null in the WHERE clause should be sufficient to prevent this error.
In addition, I get the exception when running against my test DB server but not against my development server. As far as I can tell the server are running the same version of SQL Server. The only difference is that the test server is clustered and the development server is not.
Can anyone explain why I am getting this error, why it happens on one server and not another, or how I can work around it?
Declare following statement before select query:
IF ISNULL(@event_descrip,'') = '' SET @event_descrip = '""';