USP_RESEARCHGROUPCATEGORYCODE_DELETEENTRY

/*
Generated by Blackbaud AppFx Platform
Date:  9/30/2015 1:00:47 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE procedure [dbo].[USP_RESEARCHGROUPCATEGORYCODE_DELETEENTRY]
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = NULL
)
as
begin
    set nocount on;

    /*

    453059 Jason.Perry    

    Check the RI manually. This implementation was chosen over a Trigger-style RI, or Foreign Keys, 
    to eliminate performance problems when deleting attribute categories / entries.

    */

    -- Check attribute tables for records.

    declare @CODETABLECATALOGID uniqueidentifier;

    -- Get the code table catalog id for the code table passed in. This will

    -- be used to get a list of ATTRIBUTECATEGORY records which point to the

    -- actual ATTRIBUTExxxxxxxxx table names.

    set @CODETABLECATALOGID = (select [ID] from [dbo].[CODETABLECATALOG] where [DBTABLENAME] = 'RESEARCHGROUPCATEGORYCODE');

    -- Get a list of the ATTRIBUTExxxxxxxxxxxxxxxx table names

    declare @TABLENAME nvarchar(255);
    declare @VALUECOLUMNNAME nvarchar(255);
    declare ATTRIBUTETABLECURSOR cursor local fast_forward
        for select tc.[TABLENAME], ac.[VALUECOLUMNNAME]
        from [dbo].[ATTRIBUTECATEGORY] ac
            inner join [dbo].[TABLECATALOG] tc
            on tc.[ID] = ac.[TABLECATALOGID]
        where ac.[CODETABLECATALOGID] = @CODETABLECATALOGID;

    -- Iterate the cursor and perform RI checks.

    open ATTRIBUTETABLECURSOR;
    fetch next from ATTRIBUTETABLECURSOR into @TABLENAME, @VALUECOLUMNNAME;

    while @@FETCH_STATUS = 0
    begin

        declare @COUNT int = 0;
        declare @PARAMS nvarchar(20) = N'@COUNTOUT int output';

        -- It will fail the RI check if there are any records in the associated ATTRIBUTExxxxxxxx table

        declare @SQL nvarchar(500) = 
            N'select @COUNTOUT = count(*) from [dbo].[' + @TABLENAME + 
            '] where [' + @VALUECOLUMNNAME + '] = ''' + convert(nvarchar(100), @ID) + '''';

        -- RI check

        exec sp_executesql @SQL, @PARAMS, @COUNTOUT=@COUNT output;

        if @COUNT > 0 
        begin
            close ATTRIBUTETABLECURSOR;
            deallocate ATTRIBUTETABLECURSOR;
            raiserror('Unable to delete code table entry because it is in use', 13, 1);
            return(1); -- Error

        end

        fetch next from ATTRIBUTETABLECURSOR into @TABLENAME, @VALUECOLUMNNAME;

    end

    close ATTRIBUTETABLECURSOR;
    deallocate ATTRIBUTETABLECURSOR;

    /*

    Perform delete.

    */
    declare @SEQ int
    select @SEQ = [SEQUENCE]
    from [dbo].[RESEARCHGROUPCATEGORYCODE]
    where [ID] = @ID

    -- Perform the delete

    declare @RETVAL int
    exec @RETVAL = [dbo].[USP_RESEARCHGROUPCATEGORYCODE_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID

    if @RETVAL <> 0 return @RETVAL;

    -- Fixup the sequence

    update [dbo].[RESEARCHGROUPCATEGORYCODE]
    set [SEQUENCE] = [SEQUENCE] - 1,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = getdate()
    where [SEQUENCE] > @SEQ;

    -- No error

    return(0);

end