Modular stored procedure?

[jararaca]

Hi,

I have two queries that are almost alike. The difference is that one makes a few additional joins and projects a few additional columns from those additional joins. I'd like to avoid the duplication in the parts where they are alike.

I thought of making a view of the common part, then using that in two stored procedures. One would simply run the view (FROM view), and the other would run the view with the additional joins (From view JOIN ... ).

But here's the thing about this approach: Both of the original stored procedures perform a selection operation (WHERE clause) that is defined by a parameter. With the new approach, that parameter has to remain in the (new) stored procedures (that I know, a view cannot have parameters...?), so the operations in the view no longer include a selection operation. I'm not sure how the SQL engine does its processing, but I imagine that this will decrease the efficiency of the new stored procedures relative to the original ones. Will it? And, if so, is my only option a trade-off between processing efficiency and development/maintenance efficency? Is there a better way?

Thank you,

 

[vongrunt]

> And, if so, is my only option a trade-off between processing efficiency and development/maintenance efficency?

I'd say - yes. When performance matters, go redundant. When maintenance matters, sacrifice some performance. Both within reasonable limits of course.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLSister]

a trade-off between processing efficiency and development/maintenance efficency

This is always the trade-off. The problem is that people don;t consider performance when their databases are small and over time as they grow these decision to maximize maintainability kill performance.

you could use dyunamic SQL to get the results you want but that has inherent problems of it's own interms of performance and security.

Another option is to use the If statement and have both different queries in the same sp and use a parameter to indicate which statement to run. This is usually fairly fast and easy to maintain as you see both queries at the time you are changing one.

You might even do it so the main data both will need goes to a table variable which is then joined appropriately to get the final result based on the type of query you want to run. Then you change in one place to items that affect both and in one place if either one needs something special changed that only applies to one.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jararaca]

Thank you very much for the replies. I guess I'll just leave things as they are, in that case.