using VIEWs in place of dynamic SQLs

L

Leonard Poon

I've made a form for data searching in database. Users can input the
criteria in the fields provided. Not all fields has to be inputted with
values. In other words, the "WHERE"-clause of queries have to be dynamically
constructed at runtime. I know this can be easily done in sproc. But, can it
also be done in a "View". The reason for asking this is because I heard that
the index will not be used for dynamic sql in sproc and it is not a good way
of writing queries as well. So, I keep this on mind and avoid using dynamic
sql in sproc.

I currently use the below style of "WHERE"-clause for my queries in order to
accept criteria from users.

SELECT *
FROM dbo_Object
WHERE ((Field1=@iField1 and @iField1 is not null) or @iField1 is null) and
((Field2=@strField2 and @strField2 is not null) or @strField2 is null) and
...

* the @iField1 parameter stores the value of a listbox in the data search
form,
* the @strField2 parameter stores the freeform text inputted by user as a
criteria

Is there any way I can rewrite my query to make it more efficient? Any
advice for me?
 
M

Mark

Leonard,

It seems like it would be simple to create the dynamic
query in VB with something like this

dim strSQL as string
dim strCondition as string

strSQL = "SELECT * FROM dbo_Object "
strCondition = "WHERE "

If ME.Field1 is not null then

strCondition = strCondition & "([ViewField]=" & _
Me.Field1 & ")"

end if

If ME.Field2 is not null then

if len(strCondition)>6 then

strCondition = strCondition & " AND "

end if

strCondition = strCondition & "([ViewField]=" & _
Me.Field2 & ")"

end if

....

strSQL = strSQL & strCondition

docmd.runsql strSQL

Hope that helps.

Mark
 
L

Leonard Poon

Thanks.

I guess there is no other way to avoid embedding SQL in program codes for my
case. Since my application is based on plain ASP web pages (not ASP.NET), I
just tried not to embed the queries in program codes. But, it seems this is
the only and easiest way.

Leonard
 
Top