Re: parameterised SQL views

Bob Badour wrote:

> ...
> Of course, the original original Emp table already provides everything you
> need to construct the query without the new domain. There is really no need
> for a view here:
>
> SELECT T1.Name
> FROM Emp T1, Emp T2
> WHERE T1.Name = T2.Name
> AND {12th Sep 1989} BETWEEN T1.DateFrom AND T1.DateUntil+1
> AND {25th Mar 1979} BETWEEN T2.DateFrom AND T2.DateUntil+1
> AND T1.Dept <> T2.Dept

OK, I take your point, but not all my requirements are as simple as the example I chose for my original posting.

My original scheme was non-temporal, and I had a fair number of non-trivial views, used not only from VB but also by (Access 97) reports. These views "factored out" SQL which would otherwise be replicated all over the place, and helped me debug the app (e.g. I would document what they meant, and "materialise" them (in Access "datasheet" view), and check that they looked right. I guess this is common experience...)

When I added the DateFrom and DateUntil columns to the base tables, I hoped to be able to pass parameters down through these views, but I realise I can't.

> Why do you need to compile it every time? Use bind variables:
>
> SELECT T1.Name
> FROM Emp T1, Emp T2
> WHERE T1.Name = T2.Name
> AND :1 BETWEEN T1.DateFrom AND T1.DateUntil+1
> AND :2 BETWEEN T2.DateFrom AND T2.DateUntil+1
> AND T1.Dept <> T2.Dept
>
> Compile the above once and execute it many times with different values bound
> to the query.

Hmm, thanks for this suggestion: I think I'll use this.

I guess this is a "parameterised view", but it's a pity it's per-client, not persistent? Sort of an "unstored procedure"?!

> I would stay away from stored procedures unless they are absolutely
> necessary, and they almost never are.

I instinctively don't want to mix more programming paradigms (?) than I really need, and if I can get by with a mix of dynamic SQL and my main application programming language, leaving out SPs, I guess that's good in some ways.

My reservation is that dynamic SQL queries are just strings in my source text, and aren't e.g. visible to configuration management; their dependencies aren't explicit, and they make it harder to discover e.g. what parts of my app are affected by a change to a base table's structure, or whether some table of view is still in use.

So perhaps there's a "software engineering" case for using SPs, even if they can always be avoided?

regards - Paul Received on Fri Oct 20 2000 - 16:19:56 CEST