Hi Andy / Bill,

PMFJI...

but if Bill could narrow down the rows (somewhat) with a non-parameterized SQL view, couldn't he then use a single stored procedure which accepts the parameter(s) and SELECTs the desired rows from the view while applying the parameters? I use this technique regularly for my "lookups" table, and it seems to operate quickly and smoothly.

Just my .02 worth ...


>Hi Bill
>
>>Is anyone aware of the capability of creating a parameterized SQL view that allow a common view to be executed with a passed variable that controls the scope of the records placed in the cursor...
>>... or does anyone know of a workaround that would mimic that functionality ?
>
>This is what is referred to as an 'ad-hoc' query. One in which the field list and joins are static but to which different filters can be applied. Unfortunately you cannot do this with views.
>
>There are two possible workarounds. One is to simply construct the view on the fly - save the basic script as a template and read it in when required, adding the 'WHERE' condition on the fly. Then you can simply delete the view when finished.
>
>The other is to create multiple parameterized views for all the different scenarios. Then simply open the required version under a common alias. (This is, obviously, not a very good solution if you have more than a couple of possible options).
>
>The alternative is to simply use a cursor instead of a view. You can construct the cursor on the fly and can even make it updatable (just like a view) by setting the necessary properties. The snag is that if you re-query the cursor, you have to re-set all the properties to make it updatable because cursors are closed, and re-opened, when you run a query.