USP_BUSINESSPROCESS_DELETEOUTPUTTABLES

create procedure dbo.USP_BUSINESSPROCESS_DELETEOUTPUTTABLES
(@BUSINESSPROCESSSTATUSID uniqueidentifier) with execute as owner
as
begin
    declare @TABLENAME nvarchar(128);
    declare @DBNAME nvarchar(128);
    declare @SQL nvarchar(300);

    select @DBNAME = db_name();

    declare delete_cursor cursor local fast_forward for
        select TABLENAME from dbo.BUSINESSPROCESSOUTPUT where BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID

    open delete_cursor;

    fetch next from delete_cursor into @TABLENAME;

    while @@FETCH_STATUS = 0 begin
        set @SQL = 'drop table ' + @DBNAME + '_BUSINESSPROCESS.dbo.' + @TABLENAME;
        exec sp_executesql @SQL;
        fetch next from delete_cursor into @TABLENAME
    end

    close delete_cursor;

    deallocate delete_cursor;
end