question about dummy constraint

[wireless]

I've written code that dynamically builds an sql query based on
various constraint possibilities.

The problem is the code would have been very complex had I not come up
with a dummy constraint as a kind of place holder in the statement.

To avoid complex logic that determines if there was another constraint
before any other constraint and hence the need to add, say, AND or
not, I came up with a dummy constraint so that every subsequent
constraint will begin with AND. There's no need to determine whether
to add AND or not. This makes the coding much simpler because all
that needs to be done is ask if a certain condition exists then add
the constraint along with AND in front every time.

So what I did was create the statement like this:

SELECT elapsed_time AS ET from Table1 WHERE 1 > 0 AND 1stConstraint
AND 2nd Constraint and so on.

See if the 1 > 0 condition were not there it would be necessary to
first determine the first actual contraint and not add AND in front of
it and then add the rest with ANDs in front of every one.

I should add that the user does not have to select any constraint and
that's the problem. I need to stick that WHERE 1>0 in there so that
there doesn't need to be a determination of which other, if any,
constraints are selected.

Even if my explanation above is not well understood, believe me the
front-end coding is much easier this way.

My question is does the 1 > 0 conditional check present the database
with any significant overhead or as far was dummy constraints go is
this as good as any other?

-David

[Simon Hayes]

"wireless" <wi*********@yahoo.com> wrote in message
news:90**************************@posting.google.c om...

I've written code that dynamically builds an sql query based on
various constraint possibilities.

The problem is the code would have been very complex had I not come up
with a dummy constraint as a kind of place holder in the statement.

To avoid complex logic that determines if there was another constraint
before any other constraint and hence the need to add, say, AND or
not, I came up with a dummy constraint so that every subsequent
constraint will begin with AND. There's no need to determine whether
to add AND or not. This makes the coding much simpler because all
that needs to be done is ask if a certain condition exists then add
the constraint along with AND in front every time.

So what I did was create the statement like this:

SELECT elapsed_time AS ET from Table1 WHERE 1 > 0 AND 1stConstraint
AND 2nd Constraint and so on.

See if the 1 > 0 condition were not there it would be necessary to
first determine the first actual contraint and not add AND in front of
it and then add the rest with ANDs in front of every one.

I should add that the user does not have to select any constraint and
that's the problem. I need to stick that WHERE 1>0 in there so that
there doesn't need to be a determination of which other, if any,
constraints are selected.

Even if my explanation above is not well understood, believe me the
front-end coding is much easier this way.

My question is does the 1 > 0 conditional check present the database
with any significant overhead or as far was dummy constraints go is
this as good as any other?

-David

In itself, adding such a trivial filter condition shouldn't affect anything,
but building dynamic query strings for every query isn't as robust as
executing stored procedures. If it's possible in your situation, you might
want to consider something like this:

http://www.algonet.se/~sommar/dyn-search.html

Simon

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

In itself, adding such a trivial filter condition shouldn't affect
anything, but building dynamic query strings for every query isn't as
robust as executing stored procedures. If it's possible in your
situation, you might want to consider something like this:

http://www.algonet.se/~sommar/dyn-search.html

And if you read that article, you will see that the author uses
precisely that kind of dummy condition to simplify building the
dynamic query!
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Brown]

On Mon, 22 Sep 2003 21:50:36 +0200, "Simon Hayes" <sq*@hayes.ch>
wrote:

In itself, adding such a trivial filter condition shouldn't affect anything,
but building dynamic query strings for every query isn't as robust as
executing stored procedures. If it's possible in your situation, you might
want to consider something like this:

http://www.algonet.se/~sommar/dyn-search.html

Simon, thanks. This is interesting. I had not used stored procedures
before except to implement a data loading program.

-David

[Clifford Schneider]

David,

I have dealt with this problem before and it isn't too complex to
check your SQL string for a ' where ' piece and then performing either
an operation that adds a logical operator or not adding one.

Try using a sql server function called PATINDEX.

Cliff

David Brown <wi*********@NOSPAMyahoo.com> wrote in message news:<t3********************************@4ax.com>. ..

On Mon, 22 Sep 2003 21:50:36 +0200, "Simon Hayes" <sq*@hayes.ch>
wrote:

In itself, adding such a trivial filter condition shouldn't affect anything,
but building dynamic query strings for every query isn't as robust as
executing stored procedures. If it's possible in your situation, you might
want to consider something like this:

http://www.algonet.se/~sommar/dyn-search.html

Simon, thanks. This is interesting. I had not used stored procedures
before except to implement a data loading program.

-David