SEO

#1 (**permalink**) 02-28-2008, 10:30 AM

The subject line says it all. I created a complex report as a view,
but now I have been asked to support generating it for arbitrary
dates. I know I can do it with a stored procedure, but I am wondering
if it is possible to just parameterize the views I already have. Just
thought I'd ask before sitting down to rewrite it all.

Ted

#2 (**permalink**) 02-28-2008, 10:30 AM

On Dec 3, 5:30 pm, Ted <r.ted.by...@rogers.com> wrote:
> The subject line says it all. I created a complex report as a view,
> but now I have been asked to support generating it for arbitrary
> dates. I know I can do it with a stored procedure, but I am wondering
> if it is possible to just parameterize the views I already have. Just
> thought I'd ask before sitting down to rewrite it all.
>
> Ted

Nope -- views are nothing more than "stored queries" that can be used
like tables. A query or stored procedure can select from the view
with an additional where clause, however.

#3 (**permalink**) 02-28-2008, 10:30 AM

On Dec 3, 6:36 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Dec 3, 5:30 pm, Ted <r.ted.by...@rogers.com> wrote:
>
> > The subject line says it all. I created a complex report as a view,
> > but now I have been asked to support generating it for arbitrary
> > dates. I know I can do it with a stored procedure, but I am wondering
> > if it is possible to just parameterize the views I already have. Just
> > thought I'd ask before sitting down to rewrite it all.
>
> > Ted
>
> Nope -- views are nothing more than "stored queries" that can be used
> like tables. A query or stored procedure can select from the view
> with an additional where clause, however.

I thought as much. The problem in this case is that the algorithm
used to create the view can't work with an additional where clause
after the fact. It must be applied before the data is processed. If
I do it in a stored procedure, the obvious approach would be to select
the required data into a temporary table, subject to the new where
clause, and then apply the algorithm to that table rather than the
original.

Oh well.

Ted

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Is there such a thing as a parameterized view, or must I resort to astored procedure?