USP_PROGRAMLOCATION_LOAD

The load procedure used by the view dataform template "Program Location 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.
@PROGRAMNAME	nvarchar(100)	INOUT	Name
@PROGRAMDESC	nvarchar(500)	INOUT	Description
@PROGRAMISACTIVE	bit	INOUT	Active
@SINGLELOCATIONID	uniqueidentifier	INOUT	Location ID
@SINGLELOCATIONNAME	nvarchar(100)	INOUT	Location
@LOCATIONS	xml	INOUT	LOCATIONS
@RESOURCES	xml	INOUT	Resources
@STAFFRESOURCES	xml	INOUT	Resources
@HASPRICES	bit	INOUT	Has prices
@PROGRAMCAPACITY	int	INOUT	Capacity

Definition

Copy


                CREATE procedure dbo.USP_PROGRAMLOCATION_LOAD
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit=0 output,
                    @PROGRAMNAME nvarchar(100)=null output,
                    @PROGRAMDESC nvarchar(500)=null output,
                    @PROGRAMISACTIVE bit=null output,
                    @SINGLELOCATIONID uniqueidentifier=null output,
                    @SINGLELOCATIONNAME nvarchar(100)=null output,
                    @LOCATIONS xml=null output,
                    @RESOURCES xml=null output,
                    @STAFFRESOURCES xml=null output,
                    @HASPRICES bit = null output,
                    @PROGRAMCAPACITY int = null output
                )
                as 
                set nocount on;

                set @DATALOADED = 0;

                select 
                    @DATALOADED=1,
                    @PROGRAMNAME=PROGRAM.NAME,
                    @PROGRAMDESC=PROGRAM.DESCRIPTION,
                    @PROGRAMISACTIVE=PROGRAM.ISACTIVE,
                    @LOCATIONS = dbo.UFN_PROGRAM_GETSEQUENCEDLOCATIONS_TOITEMLISTXML(@ID),
                    @RESOURCES = dbo.UFN_PROGRAMRESOURCE_GETRESOURCES_TOITEMLISTXML(@ID),
                    @STAFFRESOURCES = dbo.UFN_PROGRAMSTAFFRESOURCE_GETRESOURCESWITHJOBS_TOITEMLISTXML(@ID),
                    @HASPRICES = case when exists (select id from dbo.PROGRAMPRICE where PROGRAMPRICE.PROGRAMID = PROGRAM.ID) then
                        1
                    else
                        0
                    end,
                    @PROGRAMCAPACITY = PROGRAM.CAPACITY
                from
                    dbo.PROGRAM
                where ID=@ID;

                if @DATALOADED = 1
                begin
                    -- Need to update the EVENTLOCATIONID node to be LOCATIONID so it match the spec's collection field
                    set @LOCATIONS = (
                        select
                            T.c.value('(CAPACITY)[1]','int') as 'CAPACITY',
                            T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier') as 'LOCATIONID',
                            T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
                            T.c.value('(SEQUENCE)[1]','int') as 'SEQUENCE'
                        from
                            @LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)
                        for xml raw('ITEM'),type,elements,root('LOCATIONS'),binary base64
                    );

                    select top(1)
                        @SINGLELOCATIONID = PROGRAMLOCATION.EVENTLOCATIONID,
                        @SINGLELOCATIONNAME = NAME
                    from
                        dbo.PROGRAMLOCATION
                    inner join
                        dbo.EVENTLOCATION on PROGRAMLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
                    where
                        PROGRAMLOCATION.PROGRAMID = @ID
                    order by
                        SEQUENCE;
                end

                return 0;