Dynamic (on the fly) query

Hi all. I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query. I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results:

Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _
"[Table].[Name] = @.Name)"

But what if I have multiple items I would like query based upon what the user picks. For example, say you have five fields: Name, ID Number, Date, Address, and State. Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA. But then next time, the user only wants all data with the name of Susan. So the query is always changing and I am not sure exactly how to go about it. I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel. I've been reading a couple of the forums and I think people are using a string to pass to query the database. But I am still vague on how to approach this. Any help would be greatly appreciated!

string sql ="select pt.ProjectTaskID, pt.EmployeeID, pt.ProjectID, pt.TaskID, pt.TaskDate, pt.TaskLength " +"from ProjectTask pt, project p where pt.EmployeeID = @.Employee and (pt.TaskDate >= @.startDate and pt.TaskDate <= @.endDate) " +"and pt.projectid = p.projectid"; cmd.Parameters.AddWithValue("@.Employee", nGlobals.TheUser.EmployeeID); cmd.Parameters.AddWithValue("@.startDate", nStartDate); cmd.Parameters.AddWithValue("@.endDate", nEndDate);if (nPrimary > 0) { sql = sql +" and p.biostatPI = @.pi"; cmd.Parameters.AddWithValue("@.pi", nPrimary); }if (nClinicalPrimary > 0) { sql = sql +" and p.clinicalPI = @.clinical"; cmd.Parameters.AddWithValue("@.clinical", nClinicalPrimary); }if (nDept > 0) { sql = sql +" and p.departmentId = @.dept"; cmd.Parameters.AddWithValue("@.dept", nDept); }if ((nGrant !=null) && (nGrant.Length > 0)) { sql = sql +" and p.grantNumber = @.grant"; cmd.Parameters.AddWithValue("@.grant", nGrant); }if ((nProtocol !=null) && (nProtocol.Length > 0)) { sql = sql +" and p.protocolNumber = @.protocol"; cmd.Parameters.AddWithValue("@.protocol", nProtocol); }

|||

SELECT Name, [ID Number], [Date], Address, State FROM [Table] WHERE Name=ISNULL(@.Name, Name) AND State=ISNULL(@.State, State) AND [Date] BETWEEN @.Date1AND @.Date2

In your parameters, you need handle the default values for these parameters. Hope this gives you one direction to work with. You can also use COALESCE function instead of the ISNULL function in the where clause .

|||

In the above code, there are 5 optional selection parameters. If a value isn't blank, it's added to the where clause, and the appropriate parameter is added as well. There's a bit of overhead, because you're executing different SQL with almost every query.

A more efficient, but more complex method would be to use a stored procedure, with the base select selecting into a temp table, and for each not null criteria, deleting rows from the temp table that don't match the criteria.

|||

Ok, try this. Use a stored proc, like this.

-- first, give it every input parameter you will ever want to use to select from this table.

create procedure sometableselect (
@.sometableid int=null,
@.field1 int = null,
@.field2 varchar(50)=null,
@.fromdate datetime=null,
@.todate datetime=null,
@.sort varchar(500)=null
)
as
set nocount on
set ansi_warnings off
declare @.sql varchar(500)

-- This table below should be everything you ever wanted to know about the records
-- in this table. Get all related information and put it in here.

create table #temp1 (

Sometableid int,
field1 int,

field2 varchar(50),
recorddate datetime,
foreignkeyID int,
foreignkeydescription varchar(50),
reccount int
)

-- then insert the records you want to select
insert into #temp1 (
field1,
field2,
recorddate,
foreignkeyid
)
select
field1,
field2,
recorddate,
foreignkeyid)
from sometable
where (@.sometableid is null or @.sometableid=sometableid)
and (@.field1 is null or @.field1=field1)
and (@.field2 is null or patindex('%'+@.field2+'%', field2)>0)
and (@.fromdate is null or @.fromdate <=recorddate)
and (@.todate is null or @.todate >=recorddate)

update #temp1 set foreignkeydescription=fk.description
from foreignkeytable fk where fk.foreignkeyid = #temp1.foreignkeyid

update #temp1 set reccount = (select count(*) from #temp1)

select @.sql ='select * from #temp1 '
if isnull(@.sort,'') <>'' select @.sql = @.sql + ' order by ' + @.sort

exec(@.sql)
drop table #temp1
set nocount off
set ansi_warnings on

You call it from code like this:
query = "TableSelect "
query = query & "@.field1=" & cstr(value1)
query=query & ", @.field2='" & value2 & "'"

There are also other ways to call it, using parameters. I find building a query string to be more convenient and, in case of doubt, I can just copy and paste the query string into Query Analyzer and run it manually to see what it does.

The effect is that it will select by whatever parameters you use -- in combination -- and it will ignore those you don't use. If you don't give it a parameter, it will assume a default value of null, which means the selection criteria will drop out of the selection. If you call it with no parameters, it returns every record in the table, which makes it easy to run simple tests.

The reason you return EVERYTHING related to the record in the stored proc is to make it easier for the front-end programmer. If you write them like this, then one stored proc can be used to select records from the table almost anywhere in a typical application. One stored proc does it all.

The @.sort parameter allows you to return the recordset in whatever order you want by passing it from the front end. The @.sort may be any valid ORDER BY clause for the #temp1 table.

I also return the reccount as the number of records in the recordset. This isn't strictly necessary, but it comes in handy sometimes when you need the number of records on the front end.

If you start out an app by routinely building one of these procs for each table when you develop an app, you will find that it makes building the front end a breeze.

@.

|||Thanks all