USP_DATAFORMTEMPLATE_EDITLOAD_ANALYTICSPROJECT

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@DATE	date	INOUT
@RECALIBRATION	bit	INOUT
@RECORDSSCORED	int	INOUT
@SPECIFIEDRECORDCOUNT	bit	INOUT
@RECORDSBOUGHT	int	INOUT
@DESCRIPTION	nvarchar(max)	INOUT
@DISTRIBUTION	varbinary	INOUT
@DISTRIBUTIONFILENAME	nvarchar(255)	INOUT
@DISTRIBUTIONFILECHANGED	bit	INOUT
@ANALYTICSMODELS	xml	INOUT
@MODELSANDFREQUENCIES	xml	INOUT
@PRESENTATION1	varbinary	INOUT
@PRESENTATION2	varbinary	INOUT
@PRESENTATION3	varbinary	INOUT
@PRESENTATION4	varbinary	INOUT
@PRESENTATION5	varbinary	INOUT
@PRESENTATION1FILECHANGED	bit	INOUT
@PRESENTATION2FILECHANGED	bit	INOUT
@PRESENTATION3FILECHANGED	bit	INOUT
@PRESENTATION4FILECHANGED	bit	INOUT
@PRESENTATION5FILECHANGED	bit	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_EDITLOAD_ANALYTICSPROJECT (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @DATE date = null output,
                        @RECALIBRATION bit = null output,
                        @RECORDSSCORED integer = null output,
                        @SPECIFIEDRECORDCOUNT bit = null output,
                        @RECORDSBOUGHT integer = null output,
                        @DESCRIPTION nvarchar(max) = null output,
                        @DISTRIBUTION varbinary(max) = null output,
                        @DISTRIBUTIONFILENAME nvarchar(255) = null output,
                        @DISTRIBUTIONFILECHANGED bit = null output,
                        @ANALYTICSMODELS xml = null output,
                        @MODELSANDFREQUENCIES xml = 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,
                        @PRESENTATION1FILECHANGED bit = null output,
                        @PRESENTATION2FILECHANGED bit = null output,
                        @PRESENTATION3FILECHANGED bit = null output,
                        @PRESENTATION4FILECHANGED bit = null output,
                        @PRESENTATION5FILECHANGED bit = 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
                        set @TSLONG = 0

                        set @ANALYTICSMODELS = dbo.UFN_ANALYTICSMODEL_GETANALYTICSMODELS_TOITEMLISTXML();
                        set @MODELSANDFREQUENCIES = dbo.UFN_ANALYTICSPROJECTDISTRIBUTION_GETMODELSANDFREQUENCIES_TOITEMLISTXML(@ID);

                        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,
                            @TSLONG = ANALYTICSPROJECT.TSLONG,
                            @DATE = ANALYTICSPROJECT.DATE,
                            @RECALIBRATION = ANALYTICSPROJECT.RECALIBRATION,
                            @RECORDSSCORED = ANALYTICSPROJECT.RECORDSSCORED,
                            @RECORDSBOUGHT = ANALYTICSPROJECT.RECORDSBOUGHT,
                            @DESCRIPTION = ANALYTICSPROJECT.DESCRIPTION,
                            @DISTRIBUTION = null, /* DO NOT LOAD THE FILE HERE, IT WILL BE DOWNLOADED ON DEMAND */
                            @DISTRIBUTIONFILENAME = ANALYTICSPROJECT.DISTRIBUTIONFILENAME,
                            @DISTRIBUTIONFILECHANGED = 0,
                            @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 */
                            @PRESENTATION1FILECHANGED = 0,
                            @PRESENTATION2FILECHANGED = 0,
                            @PRESENTATION3FILECHANGED = 0,
                            @PRESENTATION4FILECHANGED = 0,
                            @PRESENTATION5FILECHANGED = 0,
                            @PRESENTATION1FILENAME = ANALYTICSPROJECT.PRESENTATION1FILENAME,
                            @PRESENTATION2FILENAME = ANALYTICSPROJECT.PRESENTATION2FILENAME,
                            @PRESENTATION3FILENAME = ANALYTICSPROJECT.PRESENTATION3FILENAME,
                            @PRESENTATION4FILENAME = ANALYTICSPROJECT.PRESENTATION4FILENAME,
                            @PRESENTATION5FILENAME = ANALYTICSPROJECT.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 ANALYTICSPROJECT.ID = @ID

                        if @RECORDSBOUGHT = 0
                            begin
                                set @SPECIFIEDRECORDCOUNT = 0;
                                set @RECORDSBOUGHT = null;
                            end
                        else
                            begin
                                set @SPECIFIEDRECORDCOUNT = 1;
                            end

                        return 0;