|
|
Posted by Erland Sommarskog on 05/10/07 21:52
Looch (lucianoj2005@yahoo.com) writes:
> I'll explain a little more. I'm just looking for a select query result
> to fill a dataset.
>
> I have a three text boxes in a VB app that I want to use as criteria
> for a query.
>
> txt1 = Serial Number
> txt2 = Ship Date
> txt3 = Item Description
>
> All three item attributes (txt1 - 3) are columns in table that
> includes much more information (Customer Name, Address, etc). Granted
> this isn't ideally following the relationtional model.
>
> If the user knows the Serial Number, that will return one record which
> would be great. If they know only the Item Description that may return
> 100 records. If they know the Item Description and Ship Date that may
> narrow it down to 25 records. I don't want to require txt1 - 3 be
> included in the query but if the information is available than it
> would be used in the query to help narrow down the search.
While the query suggested by Ed works, I would recommend this:
IF @serialnumber IS NOT NULL
BEGIN
SELECT ...
FROM tbl
WHERE serialnumber = @serialnumber
END
ELSE IF @shipdate IS NOT NULL
BEGIN
SELECT ...
FROM tbl
WHERE shipdate = @shipdate
AND itemdescription LIKE coalesce(@itemdesc, '%')
END
ELSE
BEGIN
SELECT ...
FROM tbl
WHERE itemdescription LIKE @itemdesc
END
The reason for this is performance. Assuming that the table has some
size, users don't want to wait a long time if they enter a serial
number. I assume that there is a unique index on the serial number.
I also assume that there is an index on shipdate, which is why I
single out this criteria as well. If there is no index on shipdate,
there is no point in this.
You may think: what if there are even more choices, isn't this getting
very complex then? Yes, and in that case dynamic SQL is to prefer. But
with only three choices, the above works well.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|