Export Stored Procedure - AppGeo/GPV GitHub Wiki

Given a list of IDs, this stored procedure returns content for a spreadsheet. This procedure is configured in the StoredProc column of GPVLayerFunction when Function is set to "export".

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 of spreadsheet content. Column names and data values will appear in the spreadsheet exactly as the are provided in this result set, so alias column names and format data as necessary.

Alternative Output to URL Hack

Mike Olkin has created a hack that allows a list of selected features to be exported out to as the selectionids in a GPV url. Check it out here on the Export to URL Hack page.

Basic Example

This procedure returns the owner names and complete addresses for a selected set of parcels as a spreadsheet.

SQL Server

create procedure GPVExport_Parcel
  @idlist text
as
select Owner1, Owner2, 
  own_street as OwnerStreet,
  own_city as OwnerCity,
  own_state as OwnerState,
  own_zip as OwnerZip
  from parcel_base  
  where parcel_id in (select value from ToCharTable(@idlist, DEFAULT))
  order by owner1
go

Oracle

create or replace package GPVPackage as
  type t_cursor is ref cursor;
  procedure GPVExport_Parcel(idlist in nvarchar2, io_cursor out t_cursor);
end GPVPackage;

create or replace package body GPVPackage as
  procedure GPVExport_Parcel(idlist in nvarchar2, io_cursor out t_cursor) is
    begin
    open io_cursor for select owner1 as "Owner1", 
      owner2 as "Owner2", 
      own_street as "OwnerStreet",
      own_city as "OwnerCity",
      own_state as "OwnerState",
      own_zip as "OwnerZip"
      from parcel_base 
      where parcel_id in (select * from table(ToCharTable(idlist)))
      order by owner1;
    end GPVExport_Parcel;
end GPVPackage;