USP_CODETABLE_TABLEENTRY_DATALIST

List of entries defined for a given code table
Parameters

Parameter	Parameter Type	Mode	Description
@CODETABLEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@INCLUDEINACTIVE	bit	IN	Include inactive?
@CURRENTAPPUSERID	uniqueidentifier	IN
Definition

Copy


                CREATE PROCEDURE dbo.USP_CODETABLE_TABLEENTRY_DATALIST
                (
                    @CODETABLEID uniqueidentifier,
                    @INCLUDEINACTIVE bit=1,
          @CURRENTAPPUSERID uniqueidentifier = null
                )
                with execute as owner
                AS
                    set nocount on;

                    declare @TABLENAME nvarchar(128);
                    select @TABLENAME=DBTABLENAME FROM dbo.CODETABLECATALOG WHERE ID=@CODETABLEID;
                    if @TABLENAME IS NULL return 0;

                    if @INCLUDEINACTIVE is null set @INCLUDEINACTIVE=0;

                    declare @sql nvarchar(max);

                    declare @SITETABLENAME nvarchar(128) = @TABLENAME + 'SITE'
                    if isnull(object_id(@SITETABLENAME),0) > 0 and @CURRENTAPPUSERID is null
            begin
                          set @sql='SELECT ID, DESCRIPTION, ACTIVE, SEQUENCE, (select dbo.UDA_BUILDLIST(SITE.NAME) from dbo.' + @SITETABLENAME + ' inner join dbo.site on ' + @SITETABLENAME + '.SITEID = SITE.ID where ' + @SITETABLENAME + '.' + @TABLENAME + 'ID = ' + @TABLENAME + '.ID) from  dbo.[' + @TABLENAME + ']';
            end
          else if isnull(object_id(@SITETABLENAME),0) > 0 and @CURRENTAPPUSERID is not null
            begin
                          set @sql='SELECT DISTINCT ' + @TABLENAME + '.ID, DESCRIPTION, ACTIVE, SEQUENCE, (select dbo.UDA_BUILDLIST(SITE.NAME) from dbo.' + @SITETABLENAME + ' inner join dbo.site on ' + @SITETABLENAME + '.SITEID = SITE.ID where ' + @SITETABLENAME + '.' + @TABLENAME + 'ID = ' + @TABLENAME + '.ID) from  dbo.[' + @TABLENAME + ']';
              set @sql= @sql + ' left outer join dbo.' + @SITETABLENAME + ' on ' + @TABLENAME + '.ID = ' + @SITETABLENAME + '.' + @TABLENAME + 'ID where ' + @SITETABLENAME + '.SITEID is null or ' + @SITETABLENAME + '.SITEID in (select SITEID from dbo.UFN_SITESFORUSERONCODETABLE(' + char(39) + convert(varchar(36), @CURRENTAPPUSERID) + char(39) + ', ' + char(39) + convert(varchar(36), @CODETABLEID) + char(39) + '))';
            end
                    else
                        set @sql='SELECT ID, DESCRIPTION, ACTIVE, SEQUENCE, ''All sites'' from  dbo.[' + @TABLENAME + ']';                    

                    declare @t table (ID uniqueidentifier,DESCRIPTION nvarchar(100),ACTIVE bit,SEQUENCE int, SITES nvarchar(max));

                    insert into @t exec (@sql);

                    if dbo.UFN_GETLISTSORTMETHOD(@TABLENAME) = 0
                        select ID, DESCRIPTION, ACTIVE, case when SITES = '' then 'All sites' else SITES end as SITES from @t where ACTIVE = 1 OR @INCLUDEINACTIVE=1 order by DESCRIPTION;
                    else
                        select ID, DESCRIPTION, ACTIVE, case when SITES = '' then 'All sites' else SITES end as SITES from @t where ACTIVE = 1 OR @INCLUDEINACTIVE=1 order by SEQUENCE;