USP_DATAFORMTEMPLATE_VIEW_CONSOLIDATEDPROSPECTSEARCHEXPORT

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@SOURCECODE	tinyint	INOUT
@SORTBYCODE	tinyint	INOUT
@NAMEORID	nvarchar(500)	INOUT
@LOCATION	nvarchar(500)	INOUT
@LASTNAME	nvarchar(50)	INOUT
@FIRSTNAME	nvarchar(50)	INOUT
@MIDDLENAME	nvarchar(50)	INOUT
@NICKNAME	nvarchar(50)	INOUT
@ADDRESSBLOCK	nvarchar(100)	INOUT
@CITY	nvarchar(100)	INOUT
@STATE	nvarchar(3)	INOUT
@STATEID	uniqueidentifier	INOUT
@POSTCODE	nvarchar(12)	INOUT
@INCLUDEDECEASED	bit	INOUT
@INCLUDEINACTIVE	bit	INOUT
@SPOUSELASTNAME	nvarchar(50)	INOUT
@SPOUSEFIRSTNAME	nvarchar(50)	INOUT
@AGEMIN	int	INOUT
@AGEMAX	int	INOUT
@ESTIMATEDWEALTHMINID	uniqueidentifier	INOUT
@BUSINESS	nvarchar(50)	INOUT
@CLASSYEAR	UDT_YEAR	INOUT
@RECORDTYPECODE	tinyint	INOUT
@ISEXISTING	bit	INOUT
@EXACTMATCHONLY	bit	INOUT
@OVERALLRATINGCODEID	uniqueidentifier	INOUT
@ISEXPORT	bit	INOUT
@RESEARCHRESULTS	xml	INOUT
@MAXROWS	int	INOUT
@ESTIMATEDWEALTHMIN	money	INOUT
@EXPORTCACHEID	nvarchar(100)	INOUT
@ESTIMATEDWEALTHMAXID	uniqueidentifier	INOUT
@HASBUSINESS	bit	INOUT
@GIVINGMIN	nvarchar(20)	INOUT
@LARGESTGIFTMIN	nvarchar(20)	INOUT
@SECURITIESMIN	nvarchar(20)	INOUT
@REALESTATEMIN	nvarchar(20)	INOUT
@SPENDMIN	nvarchar(20)	INOUT
@INCOMERANGE	nvarchar(30)	INOUT
@GIVINGCATEGORIES	xml	INOUT
@MOSAICS	xml	INOUT
@ESTIMATEDWEALTHMAX	money	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSOLIDATEDPROSPECTSEARCHEXPORT
(
  @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CURRENTAPPUSERID uniqueidentifier,
  @SOURCECODE tinyint = null output,
  @SORTBYCODE tinyint = null output,
  @NAMEORID nvarchar(500) = null output,
  @LOCATION nvarchar(500) = null output,
  @LASTNAME nvarchar(50) = null output,
  @FIRSTNAME nvarchar(50) = null output,
  @MIDDLENAME nvarchar(50) = null output,
  @NICKNAME nvarchar(50) = null output,
  @ADDRESSBLOCK nvarchar(100) = null output,
  @CITY nvarchar(100) = null output,
  @STATE nvarchar(3) = null output,
  @STATEID uniqueidentifier = null output,
  @POSTCODE nvarchar(12) = null output,
  @INCLUDEDECEASED bit = null output,
  @INCLUDEINACTIVE bit = null output,
  @SPOUSELASTNAME nvarchar(50) = null output,
  @SPOUSEFIRSTNAME nvarchar(50) = null output,
  @AGEMIN integer = null output,
  @AGEMAX integer = null output,
  @ESTIMATEDWEALTHMINID uniqueidentifier = null output,
  @BUSINESS nvarchar(50) = null output,
  @CLASSYEAR dbo.UDT_YEAR = null output,
  @RECORDTYPECODE tinyint = null output,
    @ISEXISTING bit = null output,
  @EXACTMATCHONLY bit = null output,
  @OVERALLRATINGCODEID uniqueidentifier = null output,
  @ISEXPORT bit = null output,
  @RESEARCHRESULTS xml = null output,
  @MAXROWS int = null output,
  @ESTIMATEDWEALTHMIN money = null output,
  @EXPORTCACHEID nvarchar(100) = null output,
  @ESTIMATEDWEALTHMAXID uniqueidentifier = null output,
  @HASBUSINESS bit = null output,
  @GIVINGMIN nvarchar(20) = null output,
  @LARGESTGIFTMIN nvarchar(20) = null output,
  @SECURITIESMIN nvarchar(20) = null output,
  @REALESTATEMIN nvarchar(20) = null output,
  @SPENDMIN nvarchar(20) = null output,
  @INCOMERANGE nvarchar(30) = null output,
  @GIVINGCATEGORIES xml = null output,
  @MOSAICS xml = null output,
  @ESTIMATEDWEALTHMAX money = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 1;

  --Set defaults
  select 
        @SOURCECODE = 3,
        @SORTBYCODE = 1,
        @RECORDTYPECODE = 1,
        @INCLUDEDECEASED = 1,
        @INCLUDEINACTIVE = 1,
        @EXACTMATCHONLY = 0;

  --Attempt to load saved parameter values
  select @ISEXISTING = 1,
         @SOURCECODE = SOURCECODE,
         @SORTBYCODE = SORTBYCODE,
         @NAMEORID = NAMEORID,
         @LOCATION = LOCATION,
         @LASTNAME = LASTNAME,
         @FIRSTNAME = FIRSTNAME,
         @MIDDLENAME = MIDDLENAME,
         @NICKNAME = NICKNAME,
         @ADDRESSBLOCK = ADDRESSBLOCK,
         @CITY = CITY,
         @STATE = STATE,
         @STATEID = STATEID,
         @POSTCODE = POSTCODE,
         @INCLUDEDECEASED = INCLUDEDECEASED,
         @INCLUDEINACTIVE = INCLUDEINACTIVE,
         @SPOUSELASTNAME = SPOUSELASTNAME,
         @SPOUSEFIRSTNAME = SPOUSEFIRSTNAME,
         @AGEMIN = AGEMIN,
         @AGEMAX = AGEMAX,
         @ESTIMATEDWEALTHMINID = ESTIMATEDWEALTHMINID,
         @ESTIMATEDWEALTHMAXID = ESTIMATEDWEALTHMAXID,
         @BUSINESS = BUSINESS,
         @HASBUSINESS = HASBUSINESS,
         @CLASSYEAR = CLASSYEAR,
         @RECORDTYPECODE = RECORDTYPECODE,
         @EXACTMATCHONLY = EXACTMATCHONLY,
         @OVERALLRATINGCODEID = OVERALLRATINGCODEID,
         @GIVINGMIN = GIVINGMIN,
         @LARGESTGIFTMIN = LARGESTGIFTMIN,
         @SECURITIESMIN = SECURITIESMIN,
         @REALESTATEMIN = REALESTATEMIN,
         @SPENDMIN = SPENDMIN,
         @INCOMERANGE = INCOMERANGE,
         @GIVINGCATEGORIES = dbo.UFN_PROSPECTSEARCH_GET_CATEGORIES_TOITEMLISTXML(@ID),
         @MOSAICS = dbo.UFN_PROSPECTSEARCH_GET_MOSAICS_TOITEMLISTXML(@ID),
         @ISEXPORT = ISEXPORT,
         @RESEARCHRESULTS = RESEARCHRESULTS,
         @MAXROWS = coalesce(MAXROWS, 100),
         @ESTIMATEDWEALTHMIN = coalesce(dbo.UFN_ESTIMATEDWEALTH_GETMINIMUMAMOUNT(ESTIMATEDWEALTHMINID), 0),
         @EXPORTCACHEID = EXPORTCACHEID,
         @ESTIMATEDWEALTHMAX = coalesce(dbo.UFN_ESTIMATEDWEALTH_GETMAXIMUMAMOUNT(ESTIMATEDWEALTHMAXID), 0)
    from dbo.PROSPECTSEARCH
    where ID = @ID;

    return 0;