Delphi - View with parameter

Question

I have the following View:

CREATE OR ALTER VIEW "ButtonGroup_ButtonNames_view"("ButtonName")
AS
select "ButtonName"
from   "ButtonGroupName_ButtonName"
where  "ButtonGroupName_ButtonName"."ButtonGroupName" = ':lButtonGroupName'

The ':lButtonGroupName' is suppoused to work as a parameter, so at runtime I can set a value to it.

When I run the application, I get the parameter name not found error. I guess this is not the correct way to declare a parameter inside a view. Is it possible anyway?

Thanks.

Answer 1

That's now how views work. You can provide parameters on the select query you use to fetch results from a view, but the view itself cannot have any parameters. There's no facility for passing parameters to a view that aren't already part of the query.

There wouldn't really be any optimization from such a "parameterized view" anyway. The database engine couldn't prefetch results because it won't know the parameter value until you do the query. Knowing that a certain column is parameterized, the database engine could make an index on that column, but you can just as easily index the column in the underlying table. The query against the view will use the same indices as a query against the table would.