USP_BENEFIT_VALIDATEUSEPERCENT

CREATE procedure dbo.USP_BENEFIT_VALIDATEUSEPERCENT
            (
                @BENEFITID uniqueidentifier,
                @USEPERCENT bit
            )
            with execute as owner

            as

            set nocount on;

            if exists(select top 1 ID from dbo.BENEFIT where ID = @BENEFITID and USEPERCENT = @USEPERCENT)
                return; --use percent not changed.


            declare @TABLENAME nvarchar(200);
            declare @COLUMNNAME nvarchar(200);
            declare @SQL nvarchar(max);
            declare @PARAMDEF nvarchar(200);
            declare @CNT int;

            --Check all FK tables for usage

            declare FKCURSOR cursor local fast_forward for
                select object_name(FKC.PARENT_OBJECT_ID),C.NAME 
                from sys.foreign_key_columns FKC
                inner join sys.columns C on FKC.PARENT_OBJECT_ID = C.[OBJECT_ID] and C.[COLUMN_ID] = FKC.[PARENT_COLUMN_ID]
                where FKC.REFERENCED_OBJECT_ID = object_id('BENEFIT')
                 and object_name(FKC.[PARENT_OBJECT_ID]) not in ('BENEFITCONSTITUENTDECLINED', 'STEWARDSHIPPLANSTEP', 'BENEFITBENEFITOPTION','BENEFITSITE');
                --To ignore FKs just add them to the in list


            open FKCURSOR;
            fetch next from FKCURSOR into @TABLENAME, @COLUMNNAME;

            while (@@FETCH_STATUS = 0)
            begin
              set @SQL = 'select @CNT = count(*) from ' + @TABLENAME + ' where ' + @COLUMNNAME + ' = @BENEFITID'
                set @PARAMDEF = N'@CNT int output, @BENEFITID uniqueidentifier';
                exec sp_executesql @SQL, @PARAMDEF, @CNT = @CNT output, @BENEFITID = @BENEFITID;

                if @CNT > 0 
                    raiserror('BBERR_BENEFIT_INUSE',13,1);

                fetch next from FKCURSOR into @TABLENAME, @COLUMNNAME;
            end

            close FKCURSOR;
            deallocate FKCURSOR;