flexibel sql select statement

[wdw]

Help

i want to have an flexibel sql statement.
so if i have filled three fields i want to sort those three fields
the same for two, four, five and others.

can anyone give me a hand here.

greetzz
Willem

 

[stustarz]

Just add "ORDER BY fieldname" at the end of the sql statement - fieldname being the name of the field you want to order by.

You could place the SQL statement into a function e.g.:

Private Sub SelectSQLSort (ByVal OrderByField As String)

Dim SQLString As String

SQLString = "SELECT fields FROM tables ORDER BY " & OrderByField

Add the variable SQLString to a datacommand, dataset select command or whatever your using - place that code here

End Sub

Say you have a button that sorts the records by the second field just call the function using

SelectSQLSort (field2)

You can do the above for any number of fields - just place the fieldname you want to sort by into the parameter brackets that are sent to the function.

 

[wdw]

Iam sorry, but i meant search instead of sort. I know how to sort,
but i need an easy way to add searchitems into a sql statement depending on the number of fields that are filled in.

example:

Select * from db where Name = "name" and Bla = "bla"

But if i have three items to search for i want to extend my sql query.

 

[quwiltw]

Heres a trimmed down version of what I use. Its for SQL Server, so itd change depending on your datasource (not sure if Access accepts optional parameters or not?). Here you can pass in any or all of the four columns and itll search against whatever you pass. If all of the columns are NOT NULL, then theres a much more elegant solution using SQL Servers COALESCE function (if this is your situation let me know and Ill post it).

CREATE PROCEDURE dbo.spAddressSelectFilter
(
	@Address1 varchar(255) = NULL,
	@Address2 varchar(255) = NULL,
	@City varchar(255) = NULL,
	@PostalCode varchar(255) = NULL,
	@SessionToken uniqueidentifier = NULL
)
AS
SELECT * 
FROM tblAddress
WHERE (( Address1 LIKE @Address1) OR (@Address1 IS NULL))AND
	 ((Address2 LIKE @Address2)OR (@Address2 IS NULL))
	 ((City LIKE @City) OR (@City IS NULL))AND
	 ((PostalCode LIKE @PostalCode) OR (@PostalCode IS NULL))

 

[wdw]

Thanks

This really helped me to get on the way, but i have also a statement with all colums not null.
So if you would like to post that method. I will be thankfull

greetzz

 

[quwiltw]

This one assumes all columns are defined as NOT NULL and I think looks much cleaner.

SELECT * 
FROM tblAddresses
WHERE Address1 LIKE COALESCE(@Address1,Address1) AND
	 Address2 LIKE COALESCE(@Address2,Address2)
	 City LIKE COALESCE(@City,City) AND
	 PostalCode LIKE COALESCE(@PostalCode, PostalCode)

I guess I should note that these queries are *really* costly with so many LIKEs, if you dont need the LIKE , then you should definitely replace them with an =.

 

[wdw]

thanks many times