Re: parameterised SQL views

Joe Celko wrote:

> Would you like to play with the idea of two or more
> copies of the same VIEW with different parmeters?

I _work_ with this idea all the time, in Prolog: the semantics are clean, declarative and a superset of the semantics which SQL realises.

> The whole conceptual model falls apart.

I don't think it's as serious as that.

If I have a (naive) table of personnel records ("this person was in this dept from this date until this date")

  Emp( Name, Dept, DateFrom, DateUntil)

and a view

  EmpSnapshot( Name, Dept, When)

as

  SELECT Name, Dept, When
  FROM Emp
  WHERE DateFrom <= When
  AND When < DateUntil

then I could use this view thus:

  SELECT T1.Name
  FROM EmpSnapshot T1,

         EmpSnapshot T2

  WHERE  T1.Name = T2.Name
  AND    T1.When = {12th Sep 1989}
  AND    T2.When = {25th Mar 1979}
  AND    T1.Dept <> T2.Dept

to find names of folk in different depts on those dates.

I don't think there's any problem with the semantics here, just that EmpSnapshot is not a *materialisable* view, so SQL baulks (and leaves me to the mercy of ad hoc vendor-specific "procedural" extensions :-)

> Stored Procedures have parameters. Each call to a procedure is not
> done inside a query where you would have those scoping problems.

Is there a consensus on how stored procedures handle parameters, and can I use them to achieve the sort of thing illustrated above?

Ones I've glanced at were more like #define textual substitutions than genuine runtime parameters, and I'd have trouble passing different actual parameter values to two instances of the same procedure...

I really need to do this: I can generate an equivalent SQL query dynamically, and pass it to the RDBMS to be compiled each time, but can I do better than this?

-- 
 _  _  Paul Singleton PhD CEng MBCS      tel:  01782 618379 (UK)
|_)(_` SmartArts Computing Consultancy   tel:  +44 1782 618379
|  ._) 36 Larchwood, Keele,            email:  p.singleton_at_keele.ac.uk
       Newcastle, Staffs  ST5 5BB  UK   road:  UK M6 J15 -> Keele Uni.