code reuse with an inline query inside a SP

Question

This below is abstraction. Queries are much more complex. Principle exactly the same.

I have a Stored Procedure that does:

SELECT * FROM T1
WHERE T1.fk IN (SELECT id FROM T2);

In reality this inline query is very complex and is being used in many other stored procedures. I wish to maintain it's code in only one place. Right now, the worst case scenario, is this inner query returns about 22K records.

How do I put this inner query in only one place (as a function, something like include,macro etc...)

Efficiency is not important in this case. Maintainability less code, is.

score 4 · Answer 1 · answered May 21 '12 at 13:59

4

a View?

CREATE VIEW MyQuery AS
 SELECT * FROM T1
 WHERE T1.fk IN (SELECT id FROM T2);

answered May 21 '12 at 13:59

Mark Brackett

84,552
17
108
152

I need to reuse ONLY the inner query – Itay Moav -Malimovka May 21 '12 at 14:44
put the inner query in a view, don't see much gain anyway if it's that simple – BlackTigerX May 21 '12 at 14:58
it is not simple, I just made it simple for the sake of a clearer question. Ill see how it goes with View – Itay Moav -Malimovka May 21 '12 at 14:59
@ItayMoav - you may need to make it less simple, if a View doesn't work for you. Perhaps a function inserting into a temp table would be a better approach, but it's difficult to tell with the current question. – Mark Brackett May 22 '12 at 00:28

code reuse with an inline query inside a SP

1 Answers1