Skip to content

Data Tab Stored Procedure

Jump to bottom Edit New page
Peter Girard edited this page Apr 18, 2017 · 8 revisions

This stored procedure fills the data tab on the bottom Selection Panel with detailed data about the selected row in the selection data grid. This procedure is configured in the StoredProc column of GPVDataTab.

Input

The first parameter will receive the data ID of the entity for which to retrieve detailed data. This is the value returned in the DataID column of the result set from a query stored procedure. If provided, the optional second parameter receives the role of the current user.

Output

One or more SQL result sets, each containing one or more rows and columns. Each row is displayed on the interface as a group of column label/column value pairs in a list. If the result set contains a column named ''Header'', the value in that field will be displayed as header text for the group. A column named Subheader will produce a second header with a lighter background under the main Header.

Links and images can be embedded in the values of the result sets.

Basic Example

This procedure returns the address and city for the specified parcel ID.

SQL Server

create procedure GPVDataTab_ParcelBasic
  @id nvarchar(26)
as 
select prop_street as Address, 
  prop_city as City
  from parcel_base
  where parcel_id = @id
go

Oracle

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

create or replace package body GPVPackage as
  procedure GPVDataTab_ParcelBasic(id in nvarchar2, io_cursor out t_cursor) is
    begin
    open io_cursor for select prop_street as "Address", 
      prop_city as "City"
      from parcel_base
      where parcel_id = id;
    end GPVDataTab_ParcelBasic;
end GPVPackage

User Examples

From Mike Olkin, Amherst MA

Here are some simplified sample "opt-out" datatabs adapted from our Public GPV; these use Where statements to conditionally show info based upon whether a property owner has opted out. When the field REM_INET_SUPPRESS = 1, the owner has opted out:

Property Tab - Property Card and Assessment fields use CASE statements to conditionally return results.

CREATE PROCEDURE GPV.GPVDataTab_PubParcelBasic
  @parcelid nvarchar(26)
  as 
  SELECT 
  a.PIN + ' / ' + ltrim(rtrim(a.Location)) as Header, 
  '''CASE WHEN b.REM_INET_SUPPRESS = 1''' THEN 'Available at Town Hall' ELSE '[http://gis.amherstma.gov/images/cards/' + ltrim(rtrim(a.PID)) + '.pdf Click Here (current through ' + convert(varchar, DATEADD(day, -1, getdate()),101)  +')]' END as [Property Card],
  a.LandUse as Landuse,
  a.Neighborhood as Neighborhood,
  cast(cast(a.Acres as money) as varchar) + ' Ac / ' +  cast(cast((a.Acres * 43560) as int) as varchar)  + ' SqFt' as [Area],
  '''CASE WHEN b.REM_INET_SUPPRESS = 1''' THEN 'Available at Town Hall' ELSE dbo.FormatCurrency(a.TotalAssessment) END as [Assessment]
    from gisadmin.TOA_CAMA_TABLE a
    inner join gisadmin.VISION_REALMAST b on a.PID = b.REM_PID
    where a.PIN = @parcelid
  GO

Sales Tab - 1st Select Statement displays results if REM_INET_SUPPRESS <> 1; 2nd Select Statement displays results if REM_INET_SUPPRESS = 1.

CREATE PROCEDURE GPV.GPVDataTab_PubParcelSales
  @parcelid nvarchar(26)
  as 
  SELECT 
  'Sale Date: ' +   CASE ISNULL(b.SLH_Sale_Date, '') WHEN '' THEN 'Not Recorded' ELSE convert(varchar, b.SLH_Sale_Date,101) END as [Header],
  CASE ISNULL(b.SLH_QUALIFIED,'') WHEN '' THEN 'Not Recorded' ELSE dbo.FormatCurrency(slh_price)  + ' (' + b.SLH_QUALIFIED + ')' END as [Sale Price],
  '[http://www.masslandrecords.com/malr/ma015 ' +  replace(ltrim(rtrim(b.SLH_Book_Pg)), ' ', '') + ']' as [Book & Page],
  b.SLH_OWN_NAME as [Buyer],
  b.SLH_OWN_ADDR as [Owner Address],
  b.SLH_OWN_ADDR2 as [ ],
  ltrim(rtrim(b.SLH_CITY + ' ' + b.SLH_STT + ' ' + b.SLH_ZIP + ' ' + b.SLH_COUNTRY)) 
    from gisadmin.VISION_SALEHIST b 
    inner join gisadmin.TOA_CAMA_TABLE a  on b.SLH_PID = a.PID
    inner join gisadmin.VISION_REALMAST c on b.SLH_PID = c.REM_PID
    where a.PIN = @parcelid and '''(CASE ISNULL(c.REM_INET_SUPPRESS,0) WHEN 0 THEN 0 ELSE c.REM_INET_SUPPRESS END)''' <> 1 
    order by b.SLH_SALE_DATE DESC
    
  SELECT 
  'Sales History Available at Town Hall' as [Header]
    from gisadmin.VISION_SALEHIST b 
    inner join gisadmin.TOA_CAMA_TABLE a  on b.SLH_PID = a.PID
    inner join gisadmin.VISION_REALMAST c on b.SLH_PID = c.REM_PID
    where a.PIN = @parcelid and '''c.REM_INET_SUPPRESS = 1'''
  GO

Full Amherst Property Data Tab - this is a beast of a stored procedure, so feel free to contact me with any questions.

This procedure returns assorted pieces of property information for the specified parcel ID, including photo thumbnails for some parcels.

CREATE PROCEDURE GPV.GPVDataTab_ParcelBasic
  @parcelid nvarchar(26)
  as 
  SELECT 
  CASE WHEN a.AlternateParcelId is null THEN a.PIN + ' / ' + ltrim(rtrim(a.Location)) 
  WHEN a.AlternateParcelId = '' THEN a.PIN + ' / ' + ltrim(rtrim(a.Location)) 
  WHEN a.AlternateParcelId like '0%' THEN a.PIN + ' / ' + ltrim(rtrim(a.Location)) 
  WHEN a.AlternateParcelId like '1%' THEN a.PIN + ' / ' + ltrim(rtrim(a.Location)) 
  WHEN a.AlternateParcelId like '2%' THEN a.PIN + ' / ' + ltrim(rtrim(a.Location))  
  WHEN a.AlternateParcelId like '6%' THEN a.PIN + ' / ' + ltrim(rtrim(a.Location))  
  ELSE a.AlternateParcelId + ' / '+ ltrim(rtrim(a.Location)) END as Header, 
  a.Owner_Name as Owner,
  a.Co_Owner_Name as " ",
  '[http://gis.amherstma.gov/images/cards/', + ltrim(rtrim(a.PID)) + '.pdf Click Here (current through ' + convert(varchar, DATEADD(day, -1, getdate()),101)  +')]' END as [Property Card],
  a.PIN as Parcel,
  a.LandUse as Landuse,
  a.Neighborhood as Neighborhood,
  cast(cast(a.Acres as money) as varchar) + ' Ac / ' + cast(cast((a.Acres * 43560) as int) as varchar) + ' SqFt' as "Area",
  dbo.FormatCurrency(a.TotalAssessment) as "Assessment"
    from gisadmin.TOA_CAMA_TABLE a
    where a.PIN = @parcelid
    
SELECT 
  CASE ISNULL(b.BLD_NOTES##1, '') WHEN '' THEN '' ELSE 'Assessment Note #' + cast(b.BLD_BLDG_NUM as char) END as [Header],
  CASE ISNULL(b.BLD_NOTES##1, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##1,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##2, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##2,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##3, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##3,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##4, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##4,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##5, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##5,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##6, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##6,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##7, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##7,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##8, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##8,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##9, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##9,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##10, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##10,'  ',' ')))  + CHAR(10) END +
  CASE ISNULL(b.BLD_NOTES##11, '') WHEN '' THEN '' ELSE ltrim(rtrim(replace(b.BLD_NOTES##11,'  ',' ')))  END 
    from gisadmin.VISION_BLDG b 
  inner join gisadmin.TOA_CAMA_TABLE a  on b.BLD_PID = a.PID
    where a.PIN = @parcelid
  order by b.BLD_BLDG_NUM
    
SELECT
  CASE ISNULL(b.TmVotes,'') WHEN '' THEN '' ELSE 'Town Meeting Vote History through 2001' END as Header,
  '[http://gis.amherstma.gov/images/TmVotes/' + replace(ltrim(rtrim(b.TmVotes)),'''','')  + ' Click Here for PDF]' as [TM Vote Info]
    from gisadmin.TOA_CAMA_TABLE a
  inner join gisadmin.TOA_PublicLandTmVotes b on a.MapParId = b.PIN
    where a.PIN = @parcelid and b.TmVotes is not null
    
SELECT
  CASE ISNULL(b.Sitename,'') WHEN '' THEN '' ELSE UPPER(replace(c.PhotoName,'.jpg','' )) END as Header,
  '[http://gis.amherstma.gov/images/PubBldgPhotos/' + replace(replace(ltrim(rtrim(c.PhotoName)),'''',''),' ','%20')  + '  http://gis.amherstma.gov/images/PubBldgPhotos/Thumbs/' + replace(replace(replace(ltrim(rtrim(c.PhotoName)),'''',''),' ','%20'),'.jpg','') + '.jpg]' as " "
    from gisadmin.TOA_CAMA_TABLE a
  inner join gisadmin.TOA_PublicLandTmVotes b on a.MapParId = b.PIN
  inner join dbo.TOA_PublicLandPhotos c on b.Sitename = left(c.PhotoName,len(b.Sitename))
    where a.PIN = @parcelid and b.SiteName is not null
  order by c.PhotoName
    
SELECT
  CASE ISNULL(b.SCAN,'') WHEN '' THEN '' ELSE 'Other Resources' END as Header,
  '[http://gis.amherstma.gov/images/scans/WaterServiceCards/' + ltrim(rtrim(b.SCAN)) + '.jpg Click Here for image]' as [Water Svc Card]
    from gisadmin.TOA_CAMA_TABLE a
  inner join gisadmin.TOA_TENG_LINKS b on a.MapParId = b.PIN
    where a.PIN = @parcelid and b.Category = 3
  GO

Home | Security | Database Schema | Web.config Settings | Stored Procedures | URL Syntax

Clone this wiki locally