I am a little confused.  You state that the problem is when the
integer field is null.  If you are searching for a value (6 for
instance) and the field is null then there should not be a match.

Which integer is null in your problem?  The field value or the
parameter?

It seems that your function is working properly but you may want to
include NULL values with any search.

If you can clear that up then I'm sure that a simple solution can be
found.

---- Original Message ----
From: dcameron@i...
To: sql_language@p...
Subject: RE: [sql_language] Return any record
Date: Wed, 9 May 2001 09:45:16 +1000 

>I am trying to produce stored procedure to run a search. The problem 
>is that
>it needs to be an AND search and not all the fields are compulsory. 
>In fact
>it is more likely to that only 1 or 2 of the 6 fields will be filled 
>out. As
>I am already performing LIKE conditions on the text fields it is 
>easy for
>them (strField LIKE '%'). The date fields are also easy as I am 
>using a
>greater than/less than criteria. The problem I have is when one the 
>integer
>fields for the search is null. Ideally in these cases you wouldn't 
>add in
>the condition to the WHERE clause, however to allow for the cases 
>when a
>value is passed through we need the conditions there. At the moment 
>I am
>reduced to generating the SQL statement. The other options (write 36 
>stored
>procs or have 1 stored proc with 36 IF blocks) are even less 
>attractive. A
>rough example of what I am talking about is below.
>
>CREATE PROCEDURE qrySearch
>@int1 Int, @int2 Int, @date1 datetime, @date2 datetime, @string1
>varChar(50), @string2 varchar(50)
>AS
>SELECT *
>FROM tblMyTable
>WHERE (strField1 LIKE @string1) AND (strField2 LIKE @string2)
>    AND (dtmField3 > @date1) AND (dtmField4 < @dtmField3)
>    AND (intField5 = @int1) AND (intField6 = @int2)
>  
>David Cameron
>nOw.b2b
>dcameron@i...
>
>$subst('Email.Unsub')
>
>