USP_JOBOCCURRENCE_VOLUNTEER_MATCHDETAILS

Returns match details for a potential volunteer job occurrence

Parameters

Parameter	Parameter Type	Mode
@JOBOCCURRENCEID	uniqueidentifier	IN
@VOLUNTEERID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@DATALOADED	bit	INOUT
@JOBID	uniqueidentifier	INOUT
@VOLUNTEERTYPE	nvarchar(50)	INOUT
@AVAILABILITY	nvarchar(max)	INOUT
@LOCATION	nvarchar(100)	INOUT

Definition

Copy



CREATE procedure dbo.USP_JOBOCCURRENCE_VOLUNTEER_MATCHDETAILS
(
    @JOBOCCURRENCEID uniqueidentifier,
    @VOLUNTEERID uniqueidentifier,
    @STARTDATE date,
    @ENDDATE date,
    @DATALOADED bit = 0 output,
    @JOBID uniqueidentifier = null output,
    @VOLUNTEERTYPE nvarchar(50) = null output,
    @AVAILABILITY nvarchar(max) = null output,
    @LOCATION nvarchar(100) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @DAYLIST nvarchar(max) = '';
    select 
        @DAYLIST = stuff((select convert(varchar, DATEADD(d,NUM,@STARTDATE), 112) 
            from 
                dbo.NUMBERS 
            where 
                DATEADD(d,NUM,@STARTDATE) <= @ENDDATE
            for xml path(''), type).value('.', 'nvarchar(max)'), 1, 0, '')

    declare @DETAILS table
    (
        JOBID uniqueidentifier,
        TIMES nvarchar(30),
        JOBNAME nvarchar(100),
        VOLUNTEERNAME nvarchar(255),
        VOLTYPE nvarchar(50),
        LOCATION nvarchar(100),
        STARTTIME dbo.UDT_HOURMINUTE,
        ENDTIME  dbo.UDT_HOURMINUTE
    )
    insert into @DETAILS
    exec dbo.USP_DATALIST_JOBOCCURRENCEMATCHEDETAIL @JOBOCCURRENCEID, @VOLUNTEERID, @DAYLIST

    select 
        @DATALOADED = 1,
        @JOBID = JOBID,
        @VOLUNTEERTYPE = VOLTYPE,
        @LOCATION = LOCATION
    from @DETAILS

    select @AVAILABILITY = stuff((
            select CHAR(10) + (CONVERT(VARCHAR(30), AVAILABLEDATE, 110) + ' ' + dbo.UFN_JOB_DISPLAYTIME(STARTTIME,ENDTIME))   
            from dbo.UFN_VOLUNTEER_AVAILABLESPECIFICDAYS(@DAYLIST, @VOLUNTEERID, 1)
            for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')

    return 0;