USP_BBNC_GETSELECTIONIDLIST

Retrieves a list of ids for all selections matching the criteria specified for BBNC.
Parameters

Parameter	Parameter Type	Mode
@NAME	nvarchar(300)	IN
@RECORDTYPE	nvarchar(50)	IN
@FORMAT	tinyint	IN
@EXACTMATCH	bit	IN
@EXCLUDEIDS	nvarchar(max)	IN
@MAXRECS	int	IN
Definition

Copy


            create procedure [dbo].[USP_BBNC_GETSELECTIONIDLIST](@NAME nvarchar(300) = '', @RECORDTYPE nvarchar(50) = '', @FORMAT tinyint = 0, @EXACTMATCH bit = 0, @EXCLUDEIDS nvarchar(max) = '', @MAXRECS int = 0)
            with execute as owner
            as

            begin
                declare @SQL nvarchar(max);
                declare @PARAMS nvarchar(max);
                declare @HASWHERE bit;

                set @HASWHERE = 0;

                set @SQL = N'select ';
                if coalesce(@MAXRECS,0) > 0
                    set @SQL = @SQL + 'top ' + convert(nvarchar, @MAXRECS + 1) + N' ';

                set @SQL = @SQL + N'BBNCIDSETIDMAP.ID ';
                set @SQL = @SQL + N'from dbo.IDSETREGISTER ';
                set @SQL = @SQL + N'inner join dbo.BBNCIDSETIDMAP on IDSETREGISTER.ID = BBNCIDSETIDMAP.IDSETREGISTERID ';
                set @SQL = @SQL + N'inner join dbo.RECORDTYPE on IDSETREGISTER.RECORDTYPEID = RECORDTYPE.ID ';
                set @PARAMS = N'';

                if len(coalesce(@NAME,'')) > 0
                    begin
                        if @EXACTMATCH = 1
                            set @SQL = @SQL + N'where IDSETREGISTER.NAME = @NAME ';
                        else
                            begin
                                set @NAME = @NAME + N'%';
                                set @SQL = @SQL + N'where IDSETREGISTER.NAME like @NAME ';
                            end

                        set @HASWHERE = 1;
                        set @PARAMS = N'@NAME nvarchar(300)';

                    end

                if len(coalesce(@RECORDTYPE,'')) > 0
                    begin
                        if @HASWHERE = 0
                            set @SQL = @SQL + N'where '
                        else
                            set @SQL = @SQL + N'and '

                        set @SQL = @SQL + N'RECORDTYPE.NAME = @RECORDTYPE ';

                        set @HASWHERE = 1;

                        if len(@PARAMS) > 0
                            set @PARAMS = @PARAMS + N', @RECORDTYPE nvarchar(50)'
                        else
                            set @PARAMS = N'@RECORDTYPE nvarchar(50)';

                    end

                if coalesce(@FORMAT,0) = 1
                    -- Dynamic

                    begin
                        if @HASWHERE = 0
                            set @SQL = @SQL + N'where '
                        else
                            set @SQL = @SQL + N'and '

                        set @SQL = @SQL + N'IDSETREGISTER.STATIC = 0 '

                        set @HASWHERE = 1;
                    end

                if coalesce(@FORMAT,0) = 2
                    -- Static

                    begin
                        if @HASWHERE = 0
                            set @SQL = @SQL + N'where '
                        else
                            set @SQL = @SQL + N'and '

                        set @SQL = @SQL + N'IDSETREGISTER.STATIC = 1 '

                        set @HASWHERE = 1;
                    end

                if len(coalesce(@EXCLUDEIDS,'')) > 0
                    begin
                        if @HASWHERE = 0
                            set @SQL = @SQL + N'where '
                        else
                            set @SQL = @SQL + N'and '

                        set @SQL = @SQL + N'not BBNCIDSETIDMAP.ID in (' + @EXCLUDEIDS + ') ';
                    end

                set @SQL = @SQL + N'order by IDSETREGISTER.NAME'

                if len(coalesce(@NAME,'')) > 0 and len(coalesce(@RECORDTYPE,'')) > 0
                    exec sp_executesql @SQL, @PARAMS, @NAME = @NAME, @RECORDTYPE = @RECORDTYPE;
                else
                    if len(coalesce(@NAME,'')) > 0
                        exec sp_executesql @SQL, @PARAMS, @NAME = @NAME;
                    else
                        if len(coalesce(@RECORDTYPE,'')) > 0
                            exec sp_executesql @SQL, @PARAMS, @RECORDTYPE = @RECORDTYPE;
                        else
                            exec sp_executesql @SQL;
            end