SQL Server : how to use variable tablename from select value

Question

 SELECT
 (
     SELECT SUM(ISNULL(Volume,0))
       FROM Order_【a1.Login】
      WHERE Login = a1.Login
 ) AS SelfVolume
 FROM dbo.Account a1

I want the table name in the sub-select (【a1.Login】) to match the value a1.Login from the outer select statement (field Login of table Account). How can I get this result?

Sounds like you've got a really bad database schema if you store the same type of information in different tables for different accounts? — Rowland Shaw
– Rowland Shaw, Commented Dec 8, 2016 at 10:18
I also think this is a bad database schema, but now there is such a demand must be done, mssql statement there is no way to achieve like this function? I tried to use function, procduce, but I still can not afford to solve this problem — Y.Dean
– Y.Dean, Commented Dec 9, 2016 at 2:26

Heinzi · Accepted Answer · 2016-12-09 07:34:28Z

11

The technical answer is: By using dynamic SQL. It's complicated, error-prone and potentially dangerous (beware of Bobby Tables). Your SQLs will become unreadable and unmaintainable. You are entering a world of pain.

The correct answer is: You don't. Don't create a separate Orders table for every user. Create one Orders table with a foreign key to your Account table.

If you still want to go ahead and work with this broken database design (remember: You are entering a world of pain, and you are just getting started), you will somehow need to construct the following SQL dynamically:

 SELECT SUM(ISNULL(Login_Volume,0)) FROM
 (
     SELECT SUM(ISNULL(Volume,0)) AS Login_Volume FROM Order_SomeUser WHERE Login = 'SomeUser'
     UNION ALL
     SELECT SUM(ISNULL(Volume,0)) AS Login_Volume FROM Order_SomeOtherUser WHERE Login = 'SomeOtherUser'
     UNION ALL
     ...
 ) AS AllSums

You can do that in the language of your choice, either in your target language (C#, Java, PHP, etc.), which is probably the easiest and most maintainable solution, or directly in T-SQL, by using T-SQL cursors and loops (= the hard way). Whichever language you choose, the algorithm is straight-forward:

Loop through your Account table and get the Logins.
- Sanitize the value and validate that the corresponding Order_ table exists.
- Create one SQL statement for each account.
- Join them with UNION ALL.
Wrap them in the outer SELECT as shown above.

Again: If there is any chance of fixing your broken DB design instead, do that, it will pay off in the long run.

edited Dec 9, 2016 at 7:34

answered Dec 8, 2016 at 10:18

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Y.Dean Over a year ago

I also think this is a bad database schema, but now there is such a demand must be done, mssql statement there is no way to achieve like this function? I tried to use function, procduce, but I still can not afford to solve this problem

Y.Dean Over a year ago

thank you for your answer sincerely. As you said, now only through the use of the cursor, CTE to achieve the results I want.

Collectives™ on Stack Overflow

SQL Server : how to use variable tablename from select value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related