Passing an ID:
The benefit here is that you simplify all the interfaces to your stored procedures.
This makes it easier to code against. If you end up calling the SP from speveral places, you just need to use a single parameter, rather than loading and passing several parameters.
Passing n Variables:
Then benefit here is that you 'decouple' your Stored Procedures from the holding table.
This means that you could simply call the stored procedures directly, without having any data in the table. This may be useful in the future, if data arrives in a new way, or for unit testing, etc.
Which is best:
I don't think that there is a straight answer to this, it's more a case of preference and opinion.
My opinion is that the less tightly coupled things are, the better. It's more flexible in the face of changes.
The way I'd do it is as follows...
CREATE PROCEDURE main_by_variable @v1 INT, @v2 INT, ...
BEGIN
EXEC sub_part_1 @v1, @v3
EXEC sub_part_2 @v2
EXEC sub_part_3 @v2, @v3
...
END
CREATE PROCEDURE main_by_id @id INT AS
BEGIN
DECLARE
@v1 INT,
@v2 INT,
...
SELECT
@v1 = field1,
@v2 = field2
FROM
holding_table
WHERE
id = @id
EXEC main_by_variable @v1, @v2, ...
END
GO;
By having the main_by_variable procedure, you have the felxibility, such as testing all the sub procedures, without actually having to enter any data into the holding table. And that flexibility is part of the sub procedures as well.
But, for convenience, you may find that using main_by_id is more tidy. As this is just a wrapper around main_by_variable, all you are doing is encapsulating a single step in the process (getting the data out of the table).
It also allows you to put a transaction around the data gathering part, and delete the data out of the table. Or many other options. It's flexible, and I like flexible.
