spDelete_SitePages

          CREATE PROCEDURE [dbo].[spDelete_SitePages]
          (
          @PKID        int,
          @CurrentUsersID    int
          )
          with execute as owner
          AS
          declare @Guid uniqueidentifier


          if (EXISTS (SELECT TOP 1 ID FROM sitepages where templatepageid=@PKID)) begin
          RAISERROR ('Unable to delete Template - This Template is in use by one or more Pages.',16,1)
          return 0
          end

          if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING where ENUMID=2 and CAST(VALUE as int)=@PKID)) begin
          RAISERROR ('This Page is configured as the Home Page in Site Settings.  It may not be deleted.',16,1)
          return 0
          end

          if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING where ENUMID=3 and CAST(VALUE as int)=@PKID)) begin
          RAISERROR ('This Page is configured as the Login Page in Site Settings.  It may not be deleted.',16,1)
          return 0
          end

          if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING where ENUMID=4 and CAST(VALUE as int)=@PKID)) begin
          RAISERROR ('This Page is configured as the Authorization Failed Page in Site Settings.  It may not be deleted.',16,1)
          return 0
          end

          if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING where ENUMID=5 and CAST(VALUE as int)=@PKID)) begin
          RAISERROR ('This Page is configured as the Privacy Page in Site Settings.  It may not be deleted.',16,1)
          return 0
          end

          if (EXISTS (SELECT TOP 1 ID FROM CMSSITESETTING where ENUMID=53 and CAST(VALUE as int)=@PKID)) begin
          RAISERROR ('This Page is configured as the Error Page in Site Settings.  It may not be deleted.',16,1)
          return 0
          end


          if (EXISTS (SELECT TOP 1 ID FROM fundraiserpages where sitepageid=@PKID)) begin
          RAISERROR ('This Page is in use by one or more Fundraisers.  It may not be deleted.',16,1)
          return 0
          end

          if (EXISTS (SELECT TOP 1 ID FROM chapterpages where sitepageid=@PKID)) begin
          RAISERROR ('This Page is in use by one or more Chapter Managers.  It may not be deleted.',16,1)
          return 0
          end

          declare @PARENTOBJECTID int;declare @PARENTCOLUMNID int;declare @REFERENCEDOBJECTID int;declare @REFERENCEDCOLUMNID int;
          declare FKCURSOR cursor local fast_forward for
              select sys.foreign_key_columns.parent_object_id, sys.foreign_key_columns.parent_column_id, sys.foreign_key_columns.referenced_object_id, sys.foreign_key_columns.referenced_column_id
              from sys.foreign_key_columns 
              inner join sys.columns on sys.foreign_key_columns.referenced_object_id = sys.columns.object_id and sys.foreign_key_columns.referenced_column_id = sys.columns.column_id
              inner join sys.foreign_keys on foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id
              where sys.foreign_key_columns.referenced_object_id = object_id('dbo.sitepages')
              and sys.foreign_keys.delete_referential_action <> 1
              and sys.foreign_keys.object_id <> object_id('FK_VanityURL_PageID')

          open FKCURSOR
          fetch next from FKCURSOR into @PARENTOBJECTID, @PARENTCOLUMNID, @REFERENCEDOBJECTID, @REFERENCEDCOLUMNID;

          while @@fetch_status = 0
          begin
              declare @SELECTSQL nvarchar(4000);
              declare @ParmDefinition nvarchar(500);
              declare @DOESEXIST bit = 0
              set @SELECTSQL = 'IF EXISTS(select top 1 SP.ID from dbo.SITEPAGES SP inner join [' + OBJECT_NAME(@PARENTOBJECTID) + '] P on P.[' + col_name(@PARENTOBJECTID, @PARENTCOLUMNID) + '] = SP.[' + col_name(@REFERENCEDOBJECTID, @REFERENCEDCOLUMNID) + '] where SP.ID = ' +CAST(@PKID as NVARCHAR(15)) + ') set @DE = 1;'
              set @ParmDefinition = '@DE int output'
              exec sp_executesql @SELECTSQL, @ParmDefinition, @DE = @DOESEXIST output

              if @DOESEXIST = 1
              begin
                  select 2
                  RAISERROR('This Page is referenced by one or more other components.',16,1)
                  return 0
              end

              fetch next from FKCURSOR into @PARENTOBJECTID, @PARENTCOLUMNID, @REFERENCEDOBJECTID, @REFERENCEDCOLUMNID;
          end

          close FKCURSOR
          deallocate FKCURSOR

          update dbo.sitepages set deleted = 1, InTrashBin = 1
          where id = @PKID