Mailing Label Stored Procedure - AppGeo/GPV GitHub Wiki

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

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 mailing label content. A label is generated for each row. Each consecutive column containing a non-null value will generate a new line of label text. Data values will appear on the labels exactly as the are provided in this result set, so format the data as necessary. Newline characters (ASCII 10) embedded in the data values will also force new lines of text on the labels. Column names are ignored.

Basic Example

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

SQL Server

create procedure GPVMailingLabels_Parcel
  @idlist text
as
select owner1, owner2, own_street,
  rtrim(own_city) + ', ' + rtrim(own_state) + '  ' + rtrim(own_zip) as citystate
  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 GPVMailingLabels_Parcel(idlist in nvarchar2, io_cursor out t_cursor);
end GPVPackage;

create or replace package body GPVPackage as
  procedure GPVMailingLabels_Parcel(idlist in nvarchar2, io_cursor out t_cursor) is
    begin
    open io_cursor for select owner1, owner2, own_street,
      rtrim(own_city) || ', ' || rtrim(own_state) || '  ' || rtrim(own_zip) as citystate
      from parcel_base 
      where parcel_id in (select * from table(ToCharTable(idlist)))
      order by owner1;
    end GPVMailingLabels_Parcel;
end GPVPackage;