Query Stored Procedure

This stored procedure fills the grid on the top Selection Panel with data about the selected features on the map. It may filter those features, only returning data for the ones that pass certain criteria. Features that are filtered out will display in gray on the map. This procedure is configured in the StoredProc column of GPVQuery.

Input

The first parameter receives a text string containing a comma-delimited list of IDs for the selected features on the map. This parameter should be defined as type TEXT / NTEXT (SQL Server) or CLOB / NCLOB (Oracle) to handle text strings of any length. If provided, the optional second parameter receives the role of the current user.

Processing

The stored procedure should convert the feature ID list to an in-memory table with the list-to-table conversion function and use this table in a correlated subquery to fetch the appropriate rows.

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. This lets the GPV highlight the feature on the map when a row is selected in the grid.
• DataID - The unique ID for this row of data. When a row is selected in the grid, the GPV sends this ID to a data tab stored procedure and shows the resulting data in the bottom Selection Panel.

It can also contain either or both of the following columns:

• ZoneID - The ID of the zone that contains the map feature. If returned, this will let the GPV present a count of the selected set by zone in the Location tab.
• LevelID - The ID of the level that contains the map feature. If returned, this will let the GPV present a count of the selected set by level in the Location tab. The GPV will automatically jump to this level when the user selects the row in the query grid.

None of the columns above will be displayed on the grid.

Normally, when map features have a one-to-one relationship with database rows, the MapID and DataID should be set to the same value. However, the GPV does support a one-to-many relationship between map features and database rows, such as the relationship between parcel polygons and condominiums. In such cases, the procedure should return multiple rows with the same MapID and different DataIDs. See Map and Data IDs for more information.

Basic Example

This procedure returns the address, city and size in acres for properties that are greater than 10 acres.

SQL Server

    create procedure GPVQuery_ParcelsLarge
    @idlist ntext
    as
    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 b.gpin in (select value from ToCharTable(@idlist, DEFAULT)) 
      and a.legal_acreage >= 10
      order by a.prop_street
    go

Oracle

    create or replace package GPVPackage as
      type t_cursor is ref cursor;
      procedure GPVQuery_ParcelsLarge(gpinlist in nclob, io_cursor out t_cursor);
    end GPVPackage;
    
    create or replace package body GPVPackage as
      procedure GPVQuery_ParcelsLarge(idlist in nvarchar2, io_cursor out t_cursor) is
        begin
        open io_cursor for 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 b.gpin in (select * from table(ToCharTable(idlist)))
          and a.legal_acreage >= 10
          order by a.prop_street;
        end GPVQuery_ParcelsLarge;
    end GPVPackage;