StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm guessing here but what you may want is </p> <ul> <li>IF parameter <code>@LetterCreated</code> is null then it should not be used as a filter at all</li> <li>IF the data in <code>RedFlagData.LETTERCREATETIME</code> is Null then it should be matched to a filter date of <code>'07/05/81'</code> FWR</li> </ul> <p>Assuming that you have the 'allow nulls' check on the <code>RDL/RDLC</code> set for the <code>@LetterCreated</code> <code>parameter</code>, the <code>where</code> needs to be changed, the optional filter can be set like so:</p> <pre><code>ISNULL(@LetterCreated, LETTERCREATETIME) = ISNULL(LETTERCREATETIME, '07/05/81') </code></pre> <p>If you get rid of the magic date, then you can guarantee no date filter applied even if <code>LETTERCREATETIME</code> is null, by the filter:</p> <pre><code>ISNULL(@LetterCreated, LETTERCREATETIME) = ISNULL(LETTERCREATETIME) OR (@LetterCreated IS NULL AND LETTERCREATETIME IS NULL) </code></pre> <p>Thus:</p> <pre><code>ALTER PROC XYZ ( @LetterCreated DATETIME, @FirstName VARCHAR(20) = NULL, @LastName VARCHAR(20) = NULL ) as begin select LETTERCREATETIME, Firstname, LastName, From RedFlagAddress where ( ISNULL(@LetterCreated, LETTERCREATETIME) = LETTERCREATETIME OR (@LetterCreated IS NULL AND LETTERCREATETIME IS NULL) ) AND LastName LIKE ISNULL(@LastName, '') + '%' AND FirstName LIKE ISNULL(@FirstName, '') + '%' end </code></pre> <p>One caveat : The performance of this query will be terrible, given the amount of functional manipulation in the where clause, and the <code>LIKEs</code> and <code>ORs</code> - Hopefully <code>RedFlagAddress</code> is a relatively small table? If not, you may need to <a href="https://softwareengineering.stackexchange.com/questions/160031/best-method-to-implement-a-filtered-search">rethink your approach</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload