USP_BBNC_TABLEENTRY_GETLIST

            CREATE procedure [dbo].[USP_BBNC_TABLEENTRY_GETLIST](@TABLENAME sysname, @INCLUDEINACTIVE bit)
            with execute as owner
            as
                set nocount on;

                declare @SQL nvarchar(max);
                declare @MAPPEDID int;
                declare @MATCHES int;
                declare @PARAMS nvarchar(40);
                declare @CODETABLECATALOGID uniqueidentifier;
                select 
                    @CODETABLECATALOGID = ID
                from
                    dbo.CODETABLECATALOG
                where
                    CODETABLECATALOG.DBTABLENAME = @TABLENAME;

                if @CODETABLECATALOGID is null
                    begin
                        declare @ERR nvarchar(max);
                        if @TABLENAME is not null
                            set @ERR = 'Unknown code table (' + @TABLENAME + ') specified.'
                        else
                            set @ERR = 'Unknown code table specified.';

                        raiserror (@ERR,13,1);
                        return 0;
                    end

                select top 1 @MAPPEDID = ID from dbo.BBNCCODETABLEIDMAP where CODETABLECATALOGID = @CODETABLECATALOGID;
                set @MATCHES = 0;
                if @MAPPEDID is null
                    if @TABLENAME is not null
                        begin
                            set @SQL = N'select @MATCHES = count(*) from dbo.' + @TABLENAME;
                            set @PARAMS = N'@MATCHES int output';

                            exec sp_executesql @SQL, @PARAMS, @MATCHES = @MATCHES output;
                        end;

                if @MATCHES > 0
                    begin
                        if @TABLENAME is not null
                            set @ERR = 'The code table (' + @TABLENAME + ') has not been mapped.'
                        else
                            set @ERR = 'This code table has not been mapped.';

                        raiserror (@ERR,13,1);
                        return 0;
                    end

                set @SQL = 'select ' + @TABLENAME + '.ID, BBNCCODETABLEIDMAP.ID as BBNCID, ' + @TABLENAME + '.DESCRIPTION ';
                set @SQL = @SQL + 'from dbo.' + @TABLENAME + ' ';
                set @SQL = @SQL + 'inner join dbo.BBNCCODETABLEIDMAP on ' + @TABLENAME + '.ID = BBNCCODETABLEIDMAP.TABLEENTRYID ';
                set @SQL = @SQL + 'and BBNCCODETABLEIDMAP.CODETABLECATALOGID = ''' + convert(nvarchar(36), @CODETABLECATALOGID) + ''' ';

                if @INCLUDEINACTIVE = 0
                    set @SQL = @SQL + 'where ' + @TABLENAME + '.ACTIVE = 1 ';

                if dbo.UFN_GETLISTSORTMETHOD(@TABLENAME) = 0
                        set @SQL = @SQL + 'order by ' + @TABLENAME + '.DESCRIPTION'
                else
                        set @SQL = @SQL + 'order by ' + @TABLENAME + '.SEQUENCE'

                exec sp_executesql @SQL;