using VIEWs in place of dynamic SQLs

  • Thread starter Thread starter Leonard Poon
  • Start date Start date
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?
 
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
 
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
 
Back
Top