USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUESTALERTDATA

The load procedure used by the view dataform template "Prospect Research Request Alert Data View Form"

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.
@REQUESTNUMBER	int	INOUT	Request number
@STATUS	nvarchar(100)	INOUT	Status
@STATUSCHANGEREASON	nvarchar(100)	INOUT	Status change reason
@RECORDTYPE	nvarchar(14)	INOUT	Record type
@PRIORITY	nvarchar(100)	INOUT	Priority
@DUEDATE	datetime	INOUT	Due date
@REQUESTTYPE	nvarchar(100)	INOUT	Request type
@REASON	nvarchar(100)	INOUT	Request reason
@REQUESTEDBY	nvarchar(154)	INOUT	Requested by
@SUBMITTEDBY	nvarchar(154)	INOUT	Submitted by
@ASSIGNEDTO	nvarchar(154)	INOUT	Assigned to
@NUMBEROFRECORDS	int	INOUT	Number of records

Definition

Copy



                create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUESTALERTDATA (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @REQUESTNUMBER integer = null output,
                    @STATUS nvarchar(100) = null output,
                    @STATUSCHANGEREASON nvarchar(100) = null output,
                    @RECORDTYPE nvarchar(14) = null output,
                    @PRIORITY nvarchar(100) = null output,
                    @DUEDATE datetime = null output,
                    @REQUESTTYPE nvarchar(100) = null output,
                    @REASON nvarchar(100) = null output,
                    @REQUESTEDBY nvarchar(154) = null output,
                    @SUBMITTEDBY nvarchar(154) = null output,
                    @ASSIGNEDTO nvarchar(154) = null output,
                    @NUMBEROFRECORDS integer = null output
                ) as
                    set nocount on;

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

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @REQUESTNUMBER = REQUEST.[REQUESTNO],
                        @STATUS = REQUEST.[STATUS],
                        @STATUSCHANGEREASON = STATUSCHANGE.[DESCRIPTION],
                        @RECORDTYPE = REQUEST.[RECORDTYPE],
                        @PRIORITY = PRIORITY.[DESCRIPTION],
                        @DUEDATE = REQUEST.[DUEDATE],
                        @REQUESTTYPE = REQUESTTYPE.[DESCRIPTION],
                        @REASON = REASON.[DESCRIPTION],
                        @REQUESTEDBY = REQUESTEDBY.[NAME],
                        @SUBMITTEDBY = SUBMITTEDBY.[NAME],
                        @ASSIGNEDTO = ASSIGNEDTO.[NAME]
                    from
                        dbo.[PROSPECTRESEARCHREQUEST] REQUEST
                    left join
                        dbo.[PROSPECTRESEARCHREQUESTSTATUSCHANGECODE] STATUSCHANGE on STATUSCHANGE.[ID] = REQUEST.[PROSPECTRESEARCHREQUESTSTATUSCHANGECODEID]
                    left join
                        dbo.[PROSPECTRESEARCHREQUESTPRIORITYCODE] PRIORITY on PRIORITY.[ID] = REQUEST.[PROSPECTRESEARCHREQUESTPRIORITYCODEID]
                    left join
                        dbo.[PROSPECTRESEARCHREQUESTTYPECODE] REQUESTTYPE on REQUESTTYPE.[ID] = REQUEST.[PROSPECTRESEARCHREQUESTTYPECODEID]
                    left join
                        dbo.[PROSPECTRESEARCHREQUESTREASONCODE] REASON on REASON.[ID] = REQUEST.[PROSPECTRESEARCHREQUESTREASONCODEID]
                    left join
                        dbo.[CONSTITUENT] REQUESTEDBY on REQUESTEDBY.[ID] = REQUEST.[REQUESTEDBYID]
                    left join
                        dbo.[CONSTITUENT] SUBMITTEDBY on SUBMITTEDBY.[ID] = REQUEST.[SUBMITTEDBYID]
                    left join
                        dbo.[CONSTITUENT] ASSIGNEDTO on ASSIGNEDTO.[ID] = REQUEST.[ASSIGNEDTOID]
                    where
                        REQUEST.[ID] = @ID;

                    select
                        @NUMBEROFRECORDS = COUNT(REQUESTCONSTITUENT.[ID])
                    from
                        dbo.[PROSPECTRESEARCHREQUESTCONSTITUENT] REQUESTCONSTITUENT
                    where
                        REQUESTCONSTITUENT.[PROSPECTRESEARCHREQUESTID] = @ID;

                    return 0;