USP_DATAFORMTEMPLATE_VIEW_ANALYTICSPROJECTRESULTS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@RECALIBRATION	bit	INOUT
@RECORDSSCORED	int	INOUT
@RECORDSBOUGHT	int	INOUT
@DESCRIPTION	nvarchar(max)	INOUT
@PRESENTATION1	varbinary	INOUT
@PRESENTATION2	varbinary	INOUT
@PRESENTATION3	varbinary	INOUT
@PRESENTATION4	varbinary	INOUT
@PRESENTATION5	varbinary	INOUT
@PRESENTATION1FILENAME	nvarchar(255)	INOUT
@PRESENTATION2FILENAME	nvarchar(255)	INOUT
@PRESENTATION3FILENAME	nvarchar(255)	INOUT
@PRESENTATION4FILENAME	nvarchar(255)	INOUT
@PRESENTATION5FILENAME	nvarchar(255)	INOUT
@PRESENTATION1INDEX	tinyint	INOUT
@PRESENTATION2INDEX	tinyint	INOUT
@PRESENTATION3INDEX	tinyint	INOUT
@PRESENTATION4INDEX	tinyint	INOUT
@PRESENTATION5INDEX	tinyint	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ANALYTICSPROJECTRESULTS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @RECALIBRATION bit = null output,
    @RECORDSSCORED integer = null output,
    @RECORDSBOUGHT integer = null output,
    @DESCRIPTION nvarchar(max) = null output,
    @PRESENTATION1 varbinary(max) = null output,
    @PRESENTATION2 varbinary(max) = null output,
    @PRESENTATION3 varbinary(max) = null output,
    @PRESENTATION4 varbinary(max) = null output,
    @PRESENTATION5 varbinary(max) = null output,
    @PRESENTATION1FILENAME nvarchar(255) = null output,
    @PRESENTATION2FILENAME nvarchar(255) = null output,
    @PRESENTATION3FILENAME nvarchar(255) = null output,
    @PRESENTATION4FILENAME nvarchar(255) = null output,
    @PRESENTATION5FILENAME nvarchar(255) = null output,
    @PRESENTATION1INDEX tinyint = null output,
    @PRESENTATION2INDEX tinyint = null output,
    @PRESENTATION3INDEX tinyint = null output,
    @PRESENTATION4INDEX tinyint = null output,
    @PRESENTATION5INDEX tinyint = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    with
    INDEXVALUELIST as
    (
        --Force sequential indexes from 0 to 4 so that the UI model has a definitive order.
        select
            CURRENTINDEXVALUE.FILENUMBER,
            (
                row_number() over
                    (order by
                        case when CURRENTINDEXVALUE.INDEXVALUE is null then 1 else 0 end,
                        CURRENTINDEXVALUE.INDEXVALUE,
                        CURRENTINDEXVALUE.FILENUMBER
                    )
                -1
            ) as INDEXVALUE
        from
            dbo.ANALYTICSPROJECT
            outer apply
            (
                select 1 as FILENUMBER, case when ANALYTICSPROJECT.PRESENTATION1 is not null then ANALYTICSPROJECT.PRESENTATION1INDEX else null end as INDEXVALUE
                union all select 2, case when ANALYTICSPROJECT.PRESENTATION2 is not null then ANALYTICSPROJECT.PRESENTATION2INDEX else null end
                union all select 3, case when ANALYTICSPROJECT.PRESENTATION3 is not null then ANALYTICSPROJECT.PRESENTATION3INDEX else null end
                union all select 4, case when ANALYTICSPROJECT.PRESENTATION4 is not null then ANALYTICSPROJECT.PRESENTATION4INDEX else null end
                union all select 5, case when ANALYTICSPROJECT.PRESENTATION5 is not null then ANALYTICSPROJECT.PRESENTATION5INDEX else null end
            ) as CURRENTINDEXVALUE
        where
            ANALYTICSPROJECT.ID = @ID
    )
    select
        @DATALOADED = 1,
        @RECALIBRATION = RECALIBRATION,
        @RECORDSSCORED = RECORDSSCORED,
        @RECORDSBOUGHT = RECORDSBOUGHT,
        @DESCRIPTION = DESCRIPTION,
        @PRESENTATION1 = null, /* DO NOT LOAD THE FILE HERE, IT WILL BE DOWNLOADED ON DEMAND */
        @PRESENTATION2 = null, /* DO NOT LOAD THE FILE HERE, IT WILL BE DOWNLOADED ON DEMAND */
        @PRESENTATION3 = null, /* DO NOT LOAD THE FILE HERE, IT WILL BE DOWNLOADED ON DEMAND */
        @PRESENTATION4 = null, /* DO NOT LOAD THE FILE HERE, IT WILL BE DOWNLOADED ON DEMAND */
        @PRESENTATION5 = null, /* DO NOT LOAD THE FILE HERE, IT WILL BE DOWNLOADED ON DEMAND */
        @PRESENTATION1FILENAME = PRESENTATION1FILENAME,
        @PRESENTATION2FILENAME = PRESENTATION2FILENAME,
        @PRESENTATION3FILENAME = PRESENTATION3FILENAME,
        @PRESENTATION4FILENAME = PRESENTATION4FILENAME,
        @PRESENTATION5FILENAME = PRESENTATION5FILENAME,
        @PRESENTATION1INDEX = (select INDEXVALUELIST.INDEXVALUE from INDEXVALUELIST where INDEXVALUELIST.FILENUMBER = 1),
        @PRESENTATION2INDEX = (select INDEXVALUELIST.INDEXVALUE from INDEXVALUELIST where INDEXVALUELIST.FILENUMBER = 2),
        @PRESENTATION3INDEX = (select INDEXVALUELIST.INDEXVALUE from INDEXVALUELIST where INDEXVALUELIST.FILENUMBER = 3),
        @PRESENTATION4INDEX = (select INDEXVALUELIST.INDEXVALUE from INDEXVALUELIST where INDEXVALUELIST.FILENUMBER = 4),
        @PRESENTATION5INDEX = (select INDEXVALUELIST.INDEXVALUE from INDEXVALUELIST where INDEXVALUELIST.FILENUMBER = 5)
    from
        dbo.ANALYTICSPROJECT
    where
        ID = @ID;

    return 0;