USP_REPORT_PURPOSEPROFILE_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 1/27/2010 7:14:31 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.5.465.0, Culture=neutral, PublicKeyToken=null
*/
CREATE proc dbo.USP_REPORT_PURPOSEPROFILE_WITHIDSET (@IDSETID uniqueidentifier = null, @ID uniqueidentifier)
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_PURPOSEPROFILE] @ID;
    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'select    
                    DESIGNATIONLEVEL.ID, 
                    DESIGNATIONLEVEL.[NAME], 
                    DESIGNATIONLEVEL.DESCRIPTION, 
                    DLC.DESCRIPTION as CATEGORY, 
                    FPT.DESCRIPTION AS DESIGNATIONLEVELTYPE,
          DESIGNATIONLEVEL.ISACCEPTINGFUNDS,
          DESIGNATIONLEVEL.ISDISBURSINGMONEY
                from
                    dbo.DESIGNATIONLEVEL
                    inner join dbo.DESIGNATIONLEVELTYPE FPT on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = FPT.ID
                    left join dbo.DESIGNATIONLEVELCATEGORYCODE DLC on DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = DLC.ID
                     inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_33834217_3a1f_4b93_a758_494b3fc91c99] on [DESIGNATIONLEVEL].[ID] = [IDSET_33834217_3a1f_4b93_a758_494b3fc91c99].[ID] 
                where
                    (DESIGNATIONLEVEL.ID = @ID or @ID is null);';

      exec @r = sp_executesql @SQLTOEXEC, N'@ID uniqueidentifier', @ID = @ID;
    end

  return @r;