1

I have Database A & Database B. Both have a User table.

I need to write one stored procedure that will retrieve the data from the correct table depending on a @Parameter passed in.

The idea of the query (data needing returned) in the stored proc needed:

SELECT * FROM dbA.User || OR || SELECT * FROM dbB.User

I have found the following ways to accomplish this but I want to minimize duplicate code nor add performance slowness/overhead.

Possibilities on my list:

  1. Dynamic SQL - harder to maintain, adds overhead in that my understanding cannot define execution plans
  2. Create a duplicate proc or If/Else inside proc - duplicate code never good
  3. Table value function - not sure of a way to do this using it as I can't use dynamic SQL here or If/Else to tell which query to run.

At this point I am leaning towards Option #2, duplicate code, with the understanding the database setup needs to be better if we want to avoid duplicating code.

I am not saying this is a good database setup, but it is what I am faced with. I have no control over combining database A & B or changing the structure to only have one User table.

Is there any other way to take care of this issue? Or thoughts on my approach?

1
  • 3
    IMO the mantra of duplicate code never good does not often apply at the database level when it comes to performance. Commented Dec 9, 2019 at 16:07

2 Answers 2

1

Let's assume the value of @Parameter is dbA or dbB

SELECT * FROM dbA.User where @Parameter = 'dbA'
UNION ALL
SELECT * FROM dbB.User where @Parameter = 'dbB'
2
  • If I put that in a view (vwUser) say: ` SELECT *, 'dbA' AS 'Database' FROM dbA.User UNION ALL SELECT *, 'dbB' AS 'Database' FROM dbB.User ` Then called: SELECT * FROM vwUser WHERE Database = 'dbB' Would it query through the dbA rows? Is there any guarantee that it wouldn't. I played around with this a while back and didn't feel comfortable enough moving forward with this approach as taking a performance hit would be bad. Commented Dec 9, 2019 at 18:24
  • 1
    @weekendwarrior - that should be an easy test on your side :) Your original question said the code would live in a stored procedure, so that is the assumption I made with my answer. Commented Dec 9, 2019 at 18:35
0

Use option 2 - separate statements / procs.

Referencing a different table is not duplicate code. Inside a database, tables are the "unit of coding." If you're referencing a different table then you're writing new code, not duplicating existing code.

Much like in OO programming. You may have Foo.DoBuzz() and Bar.DoBuzz(). Just because both types support same-named methods it does not mean you can substitute one for the other. Just because two tables support SELECT (and maybe have same-named columns) it does not mean they can be substituted. There is no equivalent of generics in SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.