SQLServer: query by form

16 Jan 2006 12:54 PM

Peter Baranyi

Hi,

most of the times I face the following situation: I have a relatively large
table (or a result of a joined tables) from what I have to display in a grid
some records, what correspond to some criteria. So what I do is: I place some
textboxes, combos, etc. to the top of the form and below goes a grid. When
the user fills out the text boxes, and chooses the appropriate values from
the combos, he clicks on a Filter button, and I have to show the records.
My question is: how should I set up the SQL sentence? Should I place for
every criteria/text box/combo a parameter? How can I remove them, if the user
not fills some values out?

I really don't want to filter at the client side, since it can take ages to
transfer thousands of records, when the user want only 10, what matches for
him...

I give an example:
Original query: select * from A, this would give back everything...
select * from A where x1=@x1 and x2=@x2 and x3=@x3: this would be the ideal
quers, when the user fills out three field on the form.
select * from A where x1=@x1 is the ideal query, when the user fills out
only the x1 field.

Is there any direct help to create such SQL sentences, and do all the
filtering at the server side?

Thanks:

Peter

16 Jan 2006 1:22 PM

Joe

Dear Peter Baranyi,
It can write a stored procedure to accept those parameters and generate the
results for any combination of input parameter by transact sql logics.

However if it use only programming method
It may need by using complicated IF-ELSE logics

Hope can help you!

Regards
Joe Tsui

Show quoteHide quote

"Peter Baranyi" wrote:

> Hi,
>
> most of the times I face the following situation: I have a relatively large
> table (or a result of a joined tables) from what I have to display in a grid
> some records, what correspond to some criteria. So what I do is: I place some
> textboxes, combos, etc. to the top of the form and below goes a grid. When
> the user fills out the text boxes, and chooses the appropriate values from
> the combos, he clicks on a Filter button, and I have to show the records.
> My question is: how should I set up the SQL sentence? Should I place for
> every criteria/text box/combo a parameter? How can I remove them, if the user
> not fills some values out?
>
> I really don't want to filter at the client side, since it can take ages to
> transfer thousands of records, when the user want only 10, what matches for
> him...
>
> I give an example:
> Original query: select * from A, this would give back everything...
> select * from A where x1=@x1 and x2=@x2 and x3=@x3: this would be the ideal
> quers, when the user fills out three field on the form.
> select * from A where x1=@x1 is the ideal query, when the user fills out
> only the x1 field.
>
> Is there any direct help to create such SQL sentences, and do all the
> filtering at the server side?
>
> Thanks:
>
> Peter
>

16 Jan 2006 2:30 PM

Mark Rendle

You can either build up your query command bit by bit, adding parameters if
the combo box is not blank, or you can pre-create the SqlParameters for each
field, being sure to set IsNullable to true, and then construct your SQL
like this:

select * from A where ( x1=@x1 or @x1 is null) and (x2=@x2 or @x2 is null)
and (x3=@x3 or @x3 is null)

Obviously that introduces OR statements, which are not performance-friendly,
but it's easier, and would work very well if your select was in a stored
procedure.

--
----
Mark

Show quoteHide quote

"Peter Baranyi" <PeterBara***@discussions.microsoft.com> wrote in message
news:259193D6-EDCE-4829-A8CC-21EC2D4EFF80@microsoft.com...
> Hi,
>
> most of the times I face the following situation: I have a relatively
> large
> table (or a result of a joined tables) from what I have to display in a
> grid
> some records, what correspond to some criteria. So what I do is: I place
> some
> textboxes, combos, etc. to the top of the form and below goes a grid. When
> the user fills out the text boxes, and chooses the appropriate values from
> the combos, he clicks on a Filter button, and I have to show the records.
> My question is: how should I set up the SQL sentence? Should I place for
> every criteria/text box/combo a parameter? How can I remove them, if the
> user
> not fills some values out?
>
> I really don't want to filter at the client side, since it can take ages
> to
> transfer thousands of records, when the user want only 10, what matches
> for
> him...
>
> I give an example:
> Original query: select * from A, this would give back everything...
> select * from A where x1=@x1 and x2=@x2 and x3=@x3: this would be the
> ideal
> quers, when the user fills out three field on the form.
> select * from A where x1=@x1 is the ideal query, when the user fills out
> only the x1 field.
>
> Is there any direct help to create such SQL sentences, and do all the
> filtering at the server side?
>
> Thanks:
>
> Peter
>

16 Jan 2006 2:35 PM

W.G. Ryan - MVP

Assuming you're using Oracle/Sql Server or a db that allows paramaters and
control constructs like T-Sql or PL/SQL, you can use

Where ColumnName = @ParamName or @ParamName = null

for each of the values in the query. This way it will match the column
value if the user passed something in, otherwise it will just evaluate to
null since the param will be null.
Show quoteHide quote

"Peter Baranyi" <PeterBara***@discussions.microsoft.com> wrote in message
news:259193D6-EDCE-4829-A8CC-21EC2D4EFF80@microsoft.com...
> Hi,
>
> most of the times I face the following situation: I have a relatively
> large
> table (or a result of a joined tables) from what I have to display in a
> grid
> some records, what correspond to some criteria. So what I do is: I place
> some
> textboxes, combos, etc. to the top of the form and below goes a grid. When
> the user fills out the text boxes, and chooses the appropriate values from
> the combos, he clicks on a Filter button, and I have to show the records.
> My question is: how should I set up the SQL sentence? Should I place for
> every criteria/text box/combo a parameter? How can I remove them, if the
> user
> not fills some values out?
>
> I really don't want to filter at the client side, since it can take ages
> to
> transfer thousands of records, when the user want only 10, what matches
> for
> him...
>
> I give an example:
> Original query: select * from A, this would give back everything...
> select * from A where x1=@x1 and x2=@x2 and x3=@x3: this would be the
> ideal
> quers, when the user fills out three field on the form.
> select * from A where x1=@x1 is the ideal query, when the user fills out
> only the x1 field.
>
> Is there any direct help to create such SQL sentences, and do all the
> filtering at the server side?
>
> Thanks:
>
> Peter
>

17 Jan 2006 9:12 PM

Nik Hatcher

Hello,

We use this control (http://devtools.korzh.com/eq/dotnet/) for such kind of
tasks.
It allows your users to define query visually and generates SQL statement as
result.

--
With the best regards, Nik

Show quoteHide quote

"Peter Baranyi" <PeterBara***@discussions.microsoft.com> wrote in message
news:259193D6-EDCE-4829-A8CC-21EC2D4EFF80@microsoft.com...
> Hi,
>
> most of the times I face the following situation: I have a relatively large
> table (or a result of a joined tables) from what I have to display in a grid
> some records, what correspond to some criteria. So what I do is: I place some
> textboxes, combos, etc. to the top of the form and below goes a grid. When
> the user fills out the text boxes, and chooses the appropriate values from
> the combos, he clicks on a Filter button, and I have to show the records.
> My question is: how should I set up the SQL sentence? Should I place for
> every criteria/text box/combo a parameter? How can I remove them, if the user
> not fills some values out?
>
> I really don't want to filter at the client side, since it can take ages to
> transfer thousands of records, when the user want only 10, what matches for
> him...
>
> I give an example:
> Original query: select * from A, this would give back everything...
> select * from A where x1=@x1 and x2=@x2 and x3=@x3: this would be the ideal
> quers, when the user fills out three field on the form.
> select * from A where x1=@x1 is the ideal query, when the user fills out
> only the x1 field.
>
> Is there any direct help to create such SQL sentences, and do all the
> filtering at the server side?
>
> Thanks:
>
> Peter
>