Search List Stored Procedure - AppGeo/GPV GitHub Wiki

This stored procedure provides valid values for a dropdown list in a search form. It is configured in the StoredProc column of GPVSearchInputField when FieldType is set to list.

Input

This stored procedure does not normally take any parameters. If provided, the optional single parameter receives the role of the current user.

Processing

The stored procedure should retrieve all valid values for the search dropdown list from the tables and views in the database.

Output

A single SQL result set containing multiple rows and one column of valid values. Column names are ignored.

Basic Example

This procedure returns a list of real property map numbers.

SQL Server

create procedure GPVLookup_ParcelMap
as
select distinct Map
  from toa_cama_table
  order by Map
  go

Oracle

create or replace package GPVPackage as
  type t_cursor is ref cursor;
  procedure GPVLookup_ParcelMap(io_cursor out t_cursor);
end GPVPackage;
    
create or replace package body GPVPackage as
  procedure GPVLookup_ParcelMap(io_cursor out t_cursor) is
    begin
    open io_cursor for select distinct Map
      from toa_cama_table
      order by Map;
    end GPVLookup_ParcelMap;
end GPVPackage;