Here is a challenge.

Currently, we have a Query by example screen allowing the user to
complete one or more fields from a base of twenty screen fields.  We
run down the fields collection and build a dynamic sql statement based
on the fields that are filled in such as:  Select onefield, twofield
.. from table1, table2 where onefield like 'value' and
another_integer_field = code ...

This all works fine!  We actually perform the qbe in two parts,
returning only a count() from the query until the user determines that
the resulting set is acceptable (but not greater than 50).

Now we are moving from Access (used for modeling) to SQL Server and
would like to duplicate the functionality using stored procedures.
Unfortunately, I can't see any way to predefine a parameter stored
procedure that could accommodate the many possible combinations of
where clauses that can be generated by selecting one to many fields of
twenty possible.  if all fields were strings I could use a large where
accommodating all fields with 'like' comparison and insuring the
non-completed fields are defaulted to * values.  However, a number of
fields are integer codified DBCombo boxes.

Any ideas, or directions would be appreciated.  BTW, environment is
VB4.0a enterprise w/ NT 3.51 and MS SQL Server.

Thanks

-> Currently, we have a Query by example screen allowing the user to
-> complete one or more fields from a base of twenty screen fields.  We
-> run down the fields collection and build a dynamic sql statement
-> based on the fields that are filled in such as:  Select onefield,
-> twofield .. from table1, table2 where onefield like 'value' and
-> another_integer_field = code ...
->
-> This all works fine!  We actually perform the qbe in two parts,
-> returning only a count() from the query until the user determines
-> that the resulting set is acceptable (but not greater than 50).
->
-> Now we are moving from Access (used for modeling) to SQL Server and
-> would like to duplicate the functionality using stored procedures.
-> Unfortunately, I can't see any way to predefine a parameter stored
-> procedure that could accommodate the many possible combinations of
-> where clauses that can be generated by selecting one to many fields
-> of twenty possible.  if all fields were strings I could use a large
-> where accommodating all fields with 'like' comparison and insuring
-> the non-completed fields are defaulted to * values.  However, a
-> number of fields are integer codified DBCombo boxes.

If you don't need a stored procedure you can probably get away with a
SQL Passthrough query.  This allows a string to passed to SQL Server and
executed in the servers memory/cpu environment.   The VB help files have
some examples, if they're no good drop me an email and I'll send you
some example code.

Good Luck,

John Marc-Aurele

:Here is a challenge.
:
:Currently, we have a Query by example screen allowing the user to
:complete one or more fields from a base of twenty screen fields.  We
:run down the fields collection and build a dynamic sql statement based
:on the fields that are filled in such as:  Select onefield, twofield
:.. from table1, table2 where onefield like 'value' and
:another_integer_field = code ...
:
:This all works fine!  We actually perform the qbe in two parts,
:returning only a count() from the query until the user determines that
:the resulting set is acceptable (but not greater than 50).
:
:Now we are moving from Access (used for modeling) to SQL Server and
:would like to duplicate the functionality using stored procedures.
:Unfortunately, I can't see any way to predefine a parameter stored
:procedure that could accommodate the many possible combinations of
:where clauses that can be generated by selecting one to many fields of
:twenty possible.  if all fields were strings I could use a large where
:accommodating all fields with 'like' comparison and insuring the
:non-completed fields are defaulted to * values.  However, a number of
:fields are integer codified DBCombo boxes.
:
:Any ideas, or directions would be appreciated.  BTW, environment is
:VB4.0a enterprise w/ NT 3.51 and MS SQL Server.
:
:Thanks
:

:
For what you are describing, the query that is being generated is only
used on a one-time basis.  There is no real benefit to using a stored
procedure for this purpose.  Also, I don't believe that you would even
want to do it if you could... because the optimizer couldn't determine
the fastest way to execute the query if it was specified as you
describe.  The optimizer generates a "query plan" when the stored
procedure is first executed.  It is likely that in a "multi-purpose",
"generic " stored procedure that would do what you suggest, would need
to have a different query plan for each execution.

Jim