Target Parameters Stored Procedure - AppGeo/GPV GitHub Wiki

Target parameters are an alternative to specifying targetids in the URL. Using targetparams you can pass a set of input parameters which specify target features to this stored procedure. The procedure then returns the requested target IDs back to the GPV. This is especially useful when the list of target IDs would otherwise make the URL excessively long.

This procedure is configured in the StoredProc column of GPVLayerFunction when Function is set to "targetparams".

Input

Any number of input parameters can be provided. However, the last parameter must accept the current user's role even if it will not be used in processing. Thus the number of parameter values passed in the URL must be one less than the number provided by this stored procedure. Parameter values in the URL must be in the same order as the stored procedure input parameters -- the parameter names are ignored.

Processing

The procedure should construct an SQL SELECT statement around the input parameters. The parameters can be used to direct program logic (using IF or CASE statements) or as criteria in the WHERE clause.

Output

A single SQL result set containing one column of target map IDs. If multiple columns are returned, target IDs are pulled from the first column. Column names are ignored.

Basic Example

This procedure returns parcel IDs from one of two columns based on a parent ID and type.

SQL Server

create procedure GPVTargetParams_Parcels
  @parentType nvarchar(20),
  @parentId nvarchar(20),
  @userRole nvarchar(20)
as
if @parentType = 'project'
begin
  select ParcelID from Parcels where ProjectID = @parentId
end
else
begin
  select ParcelID from Parcels where ParentTractID = @parentId
end
go

Oracle

create or replace package GPVPackage as
  type t_cursor is ref cursor;
  procedure GPVTargetParams_Parcels(parentType in varchar2, parentId in varchar2, io_cursor out t_cursor);
end GPVPackage;

create or replace package body GPVPackage as
  procedure GPVTargetParams_Parcels(parentType in varchar2, parentId in varchar2, userRole in varchar2, io_cursor out t_cursor) is
    begin
      if parentType = 'project'
      begin
        open io_cursor for select ParcelID from Parcels where ProjectID = parentId
      end
      else
      begin
        open io_cursor for select ParcelID from Parcels where ParentTractID = @parentId
      end
    end GPVTargetParams_Parcels;
end GPVPackage;

More Examples