USP_ADDRESS_GETLIST_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 1/27/2010 7:11:48 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.5.465.0, Culture=neutral, PublicKeyToken=null
*/
CREATE procedure dbo.USP_ADDRESS_GETLIST_WITHIDSET (@IDSETID uniqueidentifier = null, @ONLYPRIMARY bit, @CURRENTAPPUSERID uniqueidentifier)
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_ADDRESS_GETLIST] @ONLYPRIMARY, @CURRENTAPPUSERID;
    end
  else
    begin
      if not exists(select [ID] from dbo.[IDSETREGISTER] where [ID] = @IDSETID)
        raiserror('ID set does not exist in the database.', 15, 1);

      declare @DBOBJECTNAME nvarchar(max);
      declare @DBOBJECTTYPE smallint;
      declare @SQLTOEXEC nvarchar(max);

      select
        @DBOBJECTNAME = [DBOBJECTNAME],
        @DBOBJECTTYPE = [OBJECTTYPE]
      from dbo.[IDSETREGISTER]
      where [ID] = @IDSETID;

      if left(@DBOBJECTNAME, 1) <> '['
        set @DBOBJECTNAME = '[' + @DBOBJECTNAME + ']';

      if @DBOBJECTTYPE = 1
        begin
          set @DBOBJECTNAME = @DBOBJECTNAME + '(';
          set @DBOBJECTNAME = @DBOBJECTNAME + ')';
        end
      else
        begin
          if @DBOBJECTTYPE = 2
            set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @IDSETID) + ''')';
        end

      set @SQLTOEXEC = N'set nocount on;

                declare @BPID uniqueidentifier = ''3269A1D1-31CB-4D28-945C-B7623A3EFCCA'';

                declare @BYPASSSECURITY bit;
                declare @BYPASSSITESECURITY bit;                
                exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;                

                select ADDRESS.ID, 
                    ADDRESS.ADDRESSBLOCK,
                    ADDRESS.CITY,
                    coalesce(STATE.ABBREVIATION, '''') [STATE],
                    ADDRESS.POSTCODE,
                    coalesce(COUNTRY.ABBREVIATION, '''') [COUNTRY]
                from dbo.ADDRESS
                    inner join dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, null, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY) CONSTITUENT on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
                    left join dbo.STATE on STATE.ID = ADDRESS.STATEID
                    left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID

                 inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_a2a414f1_b3c8_4080_a627_0a532962c607] on [CONSTITUENT].[ID] = [IDSET_a2a414f1_b3c8_4080_a627_0a532962c607].[ID] 

                where (@ONLYPRIMARY is null or @ONLYPRIMARY = 0) or ADDRESS.ISPRIMARY = 1
                order by ADDRESS.DATEADDED;';

      exec @r = sp_executesql @SQLTOEXEC, N'@ONLYPRIMARY bit, @CURRENTAPPUSERID uniqueidentifier', @ONLYPRIMARY = @ONLYPRIMARY, @CURRENTAPPUSERID = @CURRENTAPPUSERID;
    end

  return @r;