Search Autocomplete Stored Procedure

This stored procedure provides valid values for a search input field based on partial text entered by the user. It is configured in the StoredProc column of GPVSearchInputField when FieldType is set to autocomplete.

Input

The first parameter receives the partial text string entered by the user. If provided, the optional second parameter receives the role of the current user.

Processing

The stored procedure should search for valid values by using a LIKE condition on the input text. The GPV will show all values returned so the stored procedure should limit the number of result rows if necessary.

Output

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

Basic Example

This procedure returns valid property owner names for the search autocomplete function.

SQL Server

create procedure GPVAutocomplete_ParcelOwner
  @intext nvarchar(50)
as
select top 5 owner1
  from parcel_base
  where owner1 like @intext + '%'
  order by owner1
  go

Oracle

create or replace package GPVPackage as
  type t_cursor is ref cursor;
  procedure GPVAutocomplete_ParcelOwner(intext in nvarchar2, io_cursor out t_cursor);
end GPVPackage;
    
create or replace package body GPVPackage as
  procedure GPVAutocomplete_ParcelOwner(intext in nvarchar2, io_cursor out t_cursor) is
    begin
    open io_cursor for select * from (
      select owner1 
        from parcel_base
        where owner1 like intext || '%'
        order owner1
      ) where rownum < 5;
    end GPVAutocomplete_ParcelOwner;
end GPVPackage;