USP_DATALIST_PROGRAMRESOURCESBYPROGRAM

Lists the program resources by program.

Parameters

Parameter	Parameter Type	Mode	Description
@PROGRAMID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_PROGRAMRESOURCESBYPROGRAM
(
    @PROGRAMID uniqueidentifier
)
as
    set nocount on;

    select PROGRAMRESOURCE.ID,
        PROGRAMRESOURCE.RESOURCEID,
        RESOURCE.NAME as RESOURCENAME,
        case
            when [RESOURCE].ISPERTICKETITEM = 0 then CONVERT(varchar, PROGRAMRESOURCE.[QUANTITYNEEDED])
            else ''
        end as QUANTITYNEEDED,
        case
            when [RESOURCE].ISPERTICKETITEM = 0 then ''
            else convert(varchar, [RESOURCE].[PERTICKETQUANTITY])
        end as PERTICKETQUANTITY,
        RESOURCE.FORMATTEDPERTICKETQUANTITY,
        case
            when [RESOURCE].ISPERTICKETITEM = 0 then CONVERT(varchar, PROGRAMRESOURCE.[QUANTITYNEEDED])
        else ''
        end as QUANTITYNEEDEDSTRING
    from dbo.PROGRAMRESOURCE
    inner join dbo.PROGRAM
        on PROGRAMRESOURCE.PROGRAMID = PROGRAM.ID
    inner join dbo.[RESOURCE]
        on PROGRAMRESOURCE.RESOURCEID = [RESOURCE].ID
    where PROGRAMRESOURCE.PROGRAMID = @PROGRAMID
    order by RESOURCENAME