SQLServer: query by form

[Guest]

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

 

[Guest]

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

 

[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.

 

[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.

 

[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.