USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUESTDETAIL

The load procedure used by the view dataform template "Prospect Research Request Detail View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PRIORITY	nvarchar(150)	INOUT	Priority:
@REQUESTDATE	date	INOUT	Request date:
@REASON	nvarchar(150)	INOUT	Request reason:
@REQUESTEDBYID	uniqueidentifier	INOUT	Requested by:
@SUBMITTEDBYID	uniqueidentifier	INOUT	Submitted by:
@NOTES	nvarchar(max)	INOUT	Notes:
@RECORDTYPE	nvarchar(14)	INOUT	Record type:
@NUMBEROFCONSTITUENTS	smallint	INOUT	No. prospects:
@RESEARCHGROUP	nvarchar(150)	INOUT	Record(s):
@EVENT	nvarchar(150)	INOUT	Record(s):
@CONSTITUENTS	xml	INOUT	Record(s):
@RECORDTYPECODE	tinyint	INOUT	Record type code:

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUESTDETAIL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PRIORITY nvarchar(150) = null output,
    @REQUESTDATE date = null output,
    @REASON nvarchar(150) = null output,
    @REQUESTEDBYID uniqueidentifier = null output,
    @SUBMITTEDBYID uniqueidentifier = null output,
    @NOTES nvarchar(max) = null output,
    @RECORDTYPE nvarchar(14) = null output,
    @NUMBEROFCONSTITUENTS smallint = null output,
    @RESEARCHGROUP nvarchar(150) = null output,
    @EVENT nvarchar(150) = null output,
    @CONSTITUENTS xml = null output,
    @RECORDTYPECODE tinyint = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    select 
        @DATALOADED = 1,
        @PRIORITY = PRRP.DESCRIPTION,
        @REQUESTDATE = PRR.DATEADDED,
        @REASON = PRRR.DESCRIPTION,
        @REQUESTEDBYID = PRR.REQUESTEDBYID,
        @SUBMITTEDBYID = PRR.SUBMITTEDBYID,
        @NOTES = case when len(PRR.NOTES) > 128 then (left(PRR.NOTES, 128) + '...') else PRR.NOTES end,
        @RECORDTYPE = PRR.RECORDTYPE,
        @NUMBEROFCONSTITUENTS = (select count(ID) from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT PRRC where PRRC.PROSPECTRESEARCHREQUESTID = PRR.ID and PRRC.STATUSCODE <> 6),
        @RESEARCHGROUP = RG.NAME,
        @EVENT = E.NAME,
        @CONSTITUENTS = dbo.UFN_PROSPECTRESEARCHREQUEST_GETCONSTITUENTS_TOITEMLISTXML(@ID),
        @RECORDTYPECODE = PRR.RECORDTYPECODE
    from 
        dbo.PROSPECTRESEARCHREQUEST PRR
    left join
        dbo.PROSPECTRESEARCHREQUESTPRIORITYCODE PRRP on PRRP.ID = PRR.PROSPECTRESEARCHREQUESTPRIORITYCODEID
    left join
        dbo.PROSPECTRESEARCHREQUESTREASONCODE PRRR on PRRR.ID = PRR.PROSPECTRESEARCHREQUESTREASONCODEID
    left join
        dbo.EVENT E on E.ID = PRR.EVENTID
    left join
        dbo.RESEARCHGROUP RG on RG.ID = PRR.RESEARCHGROUPID
    where 
        PRR.ID = @ID

    return 0;