Search Stored Procedure - AppGeo/GPV GitHub Wiki

This stored procedure returns an unfinished SQL SELECT statement for performing a search. Unlike all other stored procedures in the GPV, this one does not access the tables and views in your database. Instead the GPV modifies the SELECT statement it returns with the criteria and user-supplied values from the search form. That modified SELECT statement is then executed by the GPV to perform the search.

This procedure is configured in the StoredProc column of GPVSearch.

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 return a single row and column containing the text of a SQL SELECT statement that will perform the search. This SELECT statement should specify a WHERE clause but in place of any search conditions it should provide the substitution string "{0}". When performing a search the GPV will substitute "{0}" with the ColumnNames in the GPVSearchInputField table and parameterized values provided by the user on the search form.

Output

A single SQL result set containing multiple rows and columns. The column names and data values will appear in the grid exactly as the are provided in this result set, so alias column names and format the data as necessary.

The result set must also contain the following two columns:

• MapID - The ID of the map feature. Values in this column become target IDs when the user clicks Show on Map or Show All on Map.
• DataID - The unique ID for this row of data. When a single row is double-clicked in the result grid this ID is used to fill the data tabs for the feature.

None of the columns above will be displayed on the grid. See Map and Data IDs for more information.

Basic Example

This procedure returns a SQL SELECT statement which searches for real properties.

SQL Server

create procedure GPVSearch_Parcels
as
select 'SELECT b.gpin as MapID, 
  a.parcel_id as DataId, 
  a.prop_street as Address, 
  a.prop_city as City, 
  a.legal_acreage as Acres
  from parcel_base a 
  inner join gpin_table b on a.parcel_id = b.pin
  WHERE {0} 
  order by a.prop_street'
go

Oracle

create or replace package GPVPackage as
  type t_cursor is ref cursor;
  procedure GPVSearch_Parcels(io_cursor out t_cursor);
end GPVPackage;
    
create or replace package body GPVPackage as
  procedure GPVSearch_Parcels(io_cursor out t_cursor) is
    begin
    open io_cursor for select 'SELECT b.gpin as "MapID", ' ||
      'a.parcel_id as "DataId", ' ||
      'a.prop_street as "Address", ' ||
      'a.prop_city as "City", ' ||
      'a.owner1 as "Owner", ' ||
      'a.legal_acreage as "Acres" ' ||
      'from parcel_base a ' ||
      'inner join gpin_table b on a.parcel_id = b.pin ' ||
      'WHERE {0} ' ||
      'order by a.prop_street' from dual;
    end GPVSearch_Parcels;
end GPVPackage;

Note on Security

The GPV parameterizes the search values provided by the user -- it does not substitute those values directly into the SELECT statement. This will prevent potential SQL injection attacks by malicious users.