SQL Server Service Broker - Processing stored procedures in specific schema

Question

I'm currently working on a new project where I was hoping to automate the execution of all stored procedures within a specific schema in a database dynamically. I'd like to be able to simply add the stored procedures to the specific schema (e.g. Build), and have a process that runs on a set schedule which simply iterates through all the stored procedures in the schema, and runs them in parallel.

We have an existing custom ETL system that we've built, that will let us setup a bunch of jobs, this currently relies on using multiple agent jobs, that pick up the stored procedures, and executes them. I'm hoping for our new project to use something better, and was thinking the Service Broker would be the answer.

I've investigated this product: http://partitiondb.com/go-parallel-sql/ which seems to provide some pre-built code that will allow me to queue up the procedures, however I can't extract the database they provide, apparently it has a corrupted header :-(

I'm new to the world of service brokers, and am having a little difficulty in working out how I could automatically get something to queue up a bunch of stored procedures that get executed in parallel.

Any help would be much appreciated.

Cheers

Answer 1

To paraphrase one of my favorite poets - "mo' tech, mo' problems". Service Broker is a great solution to asynchronous processing, but it doesn't seem like a good fit here. Specifically, if all you're looking to do is run a (possibly unknown) set of stored procedures on a schedule, dynamic SQL seems like a better fit to me. Something like (untested)

create or alter procedure dbo.gottaRunEmAll
as
begin
    declare p cursor fast_forward, local for
    select name
    from sys.objects
    where schema_id = schema_id('Build');

    open p;

    declare @name sysname, @sql nvarchar(max);

    while(1=1)
    begin
        fetch next from p into @name;
        if (@@rowcount <> 0)
            break;
        set @sql = concat('exec [Build].', quotename(name), ';');
        exec(@sql)
    end
    close p
    deallocate p
end

Better (imo) would be to have the above procedure maintained explicitly to call the procedures that you want and how you want them.