Specifying boundaries on select statement (Upper and lower)

[cadwalli]

Hi

Can someone explain how I can perform the following in VB.Net please.

In my old system (Progress), if we created a report we would take as input a lower boundary and an upper boundary from the user, i.e. Employee Start: / Employee End.

So, id have two variables which took the start and end employees (i.e. emp_start, emp_end).

If either of these were blank it meant to all records before or all records after, ie.e. start = "", end = "D" would show all employees from A to D.

To do this I would retrieve data from the DB by a line similar to

SELECT <fields> FROM <table> WHERE Employee_Number >= emp_start and Employee_Number <= emp_end

We would have constants set up for all fields, so if a blank was used the in a from field it would start at "A", if it was the end field it would set blank to "ZZZZZZZ"

Is there a simple way to do this is SQL.

This may be an obvious question - but SQL is still pretty new to me!

Thanks in advance
Ian

 

[jmcilhinney]

Your SQL would look something like this:

SELECT * FROM MyTable
WHERE (@Start IS NULL OR MyColumn >= @Start)
AND (@End IS NULL OR MyColumn <= @End)

You would then either pass a value or NULL (DBNull.Value in VB.NET code) to each parameter. Note that some databases, e.g. SQL Server, will allow you to use just two parameters and use their values multiple times in the one statement. Others, e.g. Access, only allow you to use each parameter value once so you would actually need two pairs of parameters for each value. That would look like this:

SELECT * FROM MyTable
WHERE (@Start1 IS NULL OR MyColumn >= @Start2)
AND (@End1 IS NULL OR MyColumn <= @End2)

In that case you would have to pass the same value or NULL to both @Start1 and @Start2 and then the same value or NULL to both @End1 and @End2. If you want to learn more about using parameters in ADO.NET then follow the Blog link in my signature and read my post on the topic.

 

[cadwalli]

Hi, thanks for your reply - that makes sense.

Im still getting used to the use of NULLs in SQL!!! I never even thought of that.

Many thanks for your help.