USP_CONSTITUENTDUPLICATESEARCHPROCESS_GETRECORDS_FORCURRENTAPPUSER

CREATE procedure USP_CONSTITUENTDUPLICATESEARCHPROCESS_GETRECORDS_FORCURRENTAPPUSER
(
    @CURRENTAPPUSERID uniqueidentifier,
    @TABLE_NAME nvarchar(256),
    @USER_TABLENAME nvarchar(256)
)
AS

declare @APPUSER_IN_NOSITEROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

if object_id('tempdb..#TEMP_DUPESINSITE') is not null
    drop table #TEMP_DUPESINSITE;

create table #TEMP_DUPESINSITE
(
    NUMBEROFRECORDS int,
    KEYCONSTITID uniqueidentifier
);

        declare @SQLTOEXEC nvarchar(max);

        --Create user specific table

        set @SQLTOEXEC = 'If OBJECT_ID(''' + @USER_TABLENAME + ''', ''U'') IS NOT NULL 
                                         DROP TABLE ' + @USER_TABLENAME + ';

                                        CREATE TABLE ' + @USER_TABLENAME + '
                                        (
                                            _key_in int identity(0,1),
                                            _key_out int,
                                            _score real,
                                            _key_CONSTITID uniqueidentifier,
                                            CONSTITID uniqueidentifier
                                        );'


                set @SQLTOEXEC += 'insert into #TEMP_DUPESINSITE select count(*),_key_constitid from 
                                                     ' + @TABLE_NAME + ' dupes 
                                                        where dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, dupes.CONSTITID, @APPUSER_IN_NOSITEROLE) = 1
                                                        group by _key_constitid having count(*) > 1;

                --Push site secure matched constituent to user specific table

                insert into ' + @USER_TABLENAME + ' (_key_CONSTITID,CONSTITID,_score)
                select dupes._key_CONSTITID, dupes.CONSTITID, dupes._score 
                from ' + @TABLE_NAME + ' dupes 
                    left join dbo.CONSTITUENT c on c.ID = dupes._key_CONSTITID 
                    left outer join dbo.ADDRESS a on a.CONSTITUENTID = c.ID and a.ISPRIMARY = 1 
                    inner join #TEMP_DUPESINSITE tmp on tmp.KEYCONSTITID = dupes._key_constitid
                    where dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, dupes._key_constitid, @APPUSER_IN_NOSITEROLE) = 1
                    and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, dupes.CONSTITID, @APPUSER_IN_NOSITEROLE) = 1
                    order by dupes._key_in;

                    --Get records for processing later in CLR stored procedure

                    select * from ' + @USER_TABLENAME + ';'

                    exec sp_executesql @SQLTOEXEC, N'@CURRENTAPPUSERID uniqueidentifier,@TABLE_NAME  nvarchar(256),@APPUSER_IN_NOSITEROLE bit, @USER_TABLENAME nvarchar(256)',
                    @CURRENTAPPUSERID = @CURRENTAPPUSERID, @TABLE_NAME = @TABLE_NAME,@APPUSER_IN_NOSITEROLE=@APPUSER_IN_NOSITEROLE,@USER_TABLENAME=@USER_TABLENAME;