stored proc

Question

stored proc

arkiboys 9,706

Hello,

I have a stored procedure which does the following:

1- filter tables and place data into #table1

2- filter tables and place data into #table2

...

Then at the end of the stored proc, select columns by joining these #tables.

I have several stored procedures which they all have the same steps above, i.e.

1

2

...

and the only difference between the stored procs is where the tables are joined

Question:

Instead of me doing copy/paste of the #tables in steps 1, 2, ...

how can I make this generic so that each stored procedure has mostly the join tables instead of all the creation of #tables, etc.?

Thank you

Naveen Kumar M 175 Reputation points

2024-12-24T06:05:29.0033333+00:00
To make your stored procedures more generic and reusable, you can modularize the creation of the #tables by placing the common logic into separate helper stored procedures or table-valued functions. The main idea is to isolate the common steps (steps 1, 2, etc.) and allow each stored procedure to focus only on the specific JOIN logic.

Solution 1: Use Helper Stored Procedures

Create helper stored procedures to encapsulate the logic for generating #table1, #table2, etc. Then, call these helper stored procedures from the main stored procedures that contain the specific JOIN logic.

Steps:

Extract the logic for creating each temporary table into separate helper stored procedures.

Use the main stored procedures to call these helper procedures and focus only on the JOIN logic.

Solution 2: Use Table-Valued Functions

Instead of using temporary tables, you can use table-valued functions (TVFs) to encapsulate the filtering logic. This approach avoids the use of #tables and makes the logic reusable across multiple procedures.

Solution 3: Use a Parameterized Approach

If the filtering logic is parameter-driven (e.g., different filtering conditions for different procedures), pass parameters to a single stored procedure that handles all the common steps.

Which Solution Should You Choose?

Helper Stored Procedures:

Best for complex logic involving temporary tables.

Allows more modularity and avoids code duplication.

Table-Valued Functions (TVFs):

Best if the logic can be expressed in a query and doesn't require temporary tables.

Easier to test and reuse across procedures.

Parameterized Approach:

Best when the filtering logic depends on input parameters and needs to be flexible.

Advantages of Modularizing

Reusability: Common logic is centralized and can be reused across multiple procedures.

Maintainability: Changes to filtering logic need to be made in only one place.

Clarity: Each stored procedure focuses on its unique JOIN logic, making it easier to understand and maintain.

Please accept the answer if you find helpful.

Thanks!

Naveen

1 answer

Your answer

Answer 1

Bruce (SqlWork.com) 77,686 Volunteer Moderator

If you use a CTE you would not need temp tables, and the optimizer could generate a better plan.

Share via

stored proc

1 answer

Your answer