Return ALL values from a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return ALL values from a field

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-09-25 : 08:51:30

Hey

I'm currently trying to make an advanced search facility for a database using an ASP front end. I'd like to make it so the user can search using any combination of criteria on the HTML form, rather than having to fill every field on it (so they can just leave some blank). Also, I'd like to let them select 'ALL' from a list box so it will return records containing any value in that field.

Example:

If I had a list box with the following options - ALL, Option1, Option2, Option3

If the user selects 'ALL', how can I return every option from that field?

So I need something like:

SELECT * FROM dbo.Table WHERE Option = ('"& What goes here?! &"')

I've tried using the multiple charactor wildcard '%' but I can't seem to make it work

Thanks!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 08:59:58

SELECT * FROM dbo.Table WHERE (Option = Option1) or
(Option = Option2) or (Option = Option3)

Or
SELECT * FROM dbo.Table WHERE Option In ( Option1,Option2,Option3)

Chirag

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-25 : 09:00:40

Something like this:

Select *
from Tbl
Where 1 = (Case
		When @Option = 'All' then 1
		Else 	Case
				When Option_Col = @Option then 1
				else 0
			end
	    end)

Harsh Athalye
India.
"Nothing is Impossible"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-25 : 09:04:15

That would be
Select *
from Tbl
where Option_Col = @Option
or @Option = 'All'

but I doubt if that's what's wanted.

I would assume that the user can select multiple options from the list (any 1 or all doesn't really make sense).
In this case you would probably send a csv list of ID's with All being represented as 0 or null.

How the query is built would depend on what the possible options are.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gyto
Posting Yak Master

144 Posts

Posted - 2006-09-25 : 09:53:33

hmm....still can't seem to make it work....

If I was dealing with a charactor field I think I could probably use the following:

If Option = "ALL" then
Option = "%"
end if

SELECT * FROM dbo.Table WHERE Option_Col = ('"& Option &"')

(I could be wrong about that?! lol)

However, IF that would be ok for a charactor field, is there a similar method I could use for a numeric field?

If not, I think I'm stumped =S

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 09:56:23

What are the options which you want to search for ??

Chirag

Gyto
Posting Yak Master

144 Posts

Posted - 2006-09-25 : 10:19:08

Ok, the options in the List box are as follows:

ANY
No Error
User Busy
Normal
Call Split
Unallocated Number
Out of Order

(These values refer to a numeric field in the database, with the exception of 'ANY', obviously)

So obviously if the user selects 'Normal', I only want to return the fields containing 'Normal' from the database column. That's fine, the problem comes when they select 'ANY'....then I need to return rows regardless of the value contained in this field.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-25 : 10:50:08

See my previous comment.
You are searching for a numeric value so you must be obtaining the numneric value for the other options somewhere. It is common to store these in the listbox with 0 (-1, or some other value for all)

then
SELECT * FROM dbo.Table WHERE Option_Col = " & Option & " or " & Option & " = 0"

You should be calling a stored procedure with the values selected though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gyto
Posting Yak Master

144 Posts

Posted - 2006-09-25 : 11:15:53

hmm.....I think that may have done it.....but I can't be sure untill I've finished coding the rest of the ASP.....I'm just hoping I've done what u said right! I'll come back if not :P haha

Thanks for all ur help, much appreciated!

TroyDotNet
Starting Member

9 Posts

Posted - 2006-09-27 : 15:55:55

Here's the way I handled a somewhat similar situation (not ideal but it works). I created a web page to enable the user to search for real estate properties based on filter criteria. One of the criterion was the geographic State (e.g. IN). The first option in the listbox was --ALL--. If the first two characters for the State parameter are "--" then the parameter is modified to search for all states using the wildcard character (this effectively enables you to change --ALL-- to -- SELECT -- etc without changing the stored procedure). Granted, in my case the user needed to search for only one state at a time, but this may give you additional insight into your dilemma.

ALTER PROCEDURE [dbo].[spSearchProperties]
(
@Name as varchar(50) = null,
@Address as varchar(50) = null,
@City as varchar(25) = null,
@State as varchar(3) = null,
@Zip as varchar(10) = null,
@Username as varchar(50),
@UserCanAccessAllEntities as int
)
AS

-- validate search criteria
IF @Name is null
BEGIN
SET @Name = '%'
END
ELSE IF LEN(@Name) > 0 and @Name <> '%'
BEGIN
SET @Name = '%' + @Name + '%'
END

IF @Address is null
BEGIN
SET @Address = '%'
END
ELSE IF LEN(@Address) > 0 and @Address <> '%'
BEGIN
SET @Address = '%' + @Address + '%'
END

IF @City is null
BEGIN
SET @City = '%'
END
ELSE IF LEN(@City) > 0 and @City <> '%'
BEGIN
SET @City = '%' + @City + '%'
END

IF @State is null
BEGIN
SET @State = '%'
END
ELSE IF LEN(@State) = 1 and @State <> '%'
BEGIN
SET @State = '%' + @State + '%'
END
ELSE IF LEFT(@State, 2) = '--'
BEGIN
SET @State = '%'
END

IF @Zip is null
BEGIN
SET @Zip = '%'
END
ELSE IF LEN(@Zip) > 0 and @Zip <> '%'
BEGIN
SET @Zip = '%' + @Zip + '%'
END

SELECT DISTINCT
P.ID,
P.Name,
P.Address,
P.Address2,
P.City,
P.State,
P.Zip,
P.Phone,
P.NumberOfUnits,

tblOwners.ID as OwnerID,
tblOwners.Name AS Owner
FROM
tblProperties P
LEFT OUTER JOIN tblPropertyOwners PO ON PO.PropertyID = P.ID
LEFT OUTER JOIN tblOwners ON tblOwners.ID = PO.OwnerID and
-- grab owner info for the first owner (lowest ID #) that is linked to the property
tblOwners.ID = (SELECT min(OwnerID) FROM tblPropertyOwners WHERE tblPropertyOwners.PropertyID = P.ID)
WHERE
-- determine if the user can view the current Property by checking whether they have carte blanche access
-- to all entities or (if they don't) checking whether their role is assigned to the Property
(
@UserCanAccessAllEntities = 1 OR P.ID in
(SELECT PropertyID FROM tblPropertyRoles
INNER JOIN aspnet_UsersInRoles ON aspnet_UsersInRoles.RoleId = tblPropertyRoles.RoleID
INNER JOIN aspnet_Users ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId
WHERE aspnet_Users.Username = @Username
)
) and

-- determine if search criteria matches Property fields
((LTRIM(RTRIM(P.Name))) Like @Name or (@Name = '%' and P.Name is null)) and
((LTRIM(RTRIM(P.Address))) Like @Address or (@Address = '%' and P.Address is null)) and
((LTRIM(RTRIM(P.City))) Like @City or (@City = '%' and P.City is null)) and
((LTRIM(RTRIM(P.State))) Like @State or (@State = '%' and P.State is null)) and
((LTRIM(RTRIM(P.Zip))) Like @Zip or (@Zip = '%' and P.Zip is null))
ORDER BY
P.Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources