CODEWITHSUNDEEP
sqlsql-server-2005t-sql
ediblecode
ediblecode

Reputation: 11971

SQL Server setting multiple variables

I have 7 different stored procedures that take in a number of parameters and insert data into 7 different tables.

I am now creating a main stored procedure to execute these 7 procedures with all of the data they need. All of the data they need is ONE table (CommonImport).

Should I take all of the parameters I need in this main stored procedure?

Or

Only take in the ID of the row that needs to be inserted into these 7 separate tables and get the data directly from the table.

I think the second option is best. BUT, how do I set all the variables in the main stored procedure to all of the data from the (CommonImport) table?

Essentially, how do I set a bunch of declared variables to the values from a specific row in the CommonImport table?

Thanks

Upvotes: 2

Views: 2505

Answers (2)

MatBailie
MatBailie

Reputation: 86808

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.

Upvotes: 2

sll
sll

Reputation: 62564

I would suggest accepting all variables as parameters and define default values for them, so SP users can use it either with single ID parameter or with an other as weel by specifying those directly

CREATE PROCEDURE MainSP
  @ID int,
  @CustomParameter  varchar(10) = NULL,
  @CustomParameter1 DateTime = NULL,
  ...

In this way SP would be pretty flexible

Upvotes: 2

Related Questions