3

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?

epotter
  • 7,631
  • 7
  • 63
  • 88
  • Possible duplicate of: [7645 Null or empty full-text predicate](http://stackoverflow.com/questions/189765/7645-null-or-empty-full-text-predicate) – Joe Stefanelli Sep 15 '10 at 15:27
  • The question you mentioned is not a duplicate in that his problem and solution are contained entirely in SQL server and I need to solve the problem in ASP side. – epotter Sep 15 '10 at 18:09

1 Answers1

3

Declare following statement before select query:

IF ISNULL(@event_descrip,'') = '' SET @event_descrip = '""';
Cosmin
  • 21,216
  • 5
  • 45
  • 60
Sam
  • 39
  • 2
  • This wont give you correct result from query. It will compare with '""' in that column rather than empty string and will not ignore the condition. – Jitendra Pancholi Sep 27 '13 at 04:34