dynamic sql embedded in select query

Question

I have a table Users,

╔════╦═══════╦══════╗
║ Id ║ Name  ║  Db  ║
╠════╬═══════╬══════╣
║  1 ║ Peter ║ DB1  ║
║  2 ║ John  ║ DB16 ║
║  3 ║ Alex  ║ DB23 ║
╚════╩═══════╩══════╝

and many databases that have the same structure (Same tables, same procedures, ...), so every database have a table named Project, and this is the structure of Project table,

╔════╦═════════╦═════════════╗
║ Id ║ Request ║ Information ║
╠════╬═════════╬═════════════╣
║  1 ║     126 ║ XB1         ║
║  2 ║     126 ║ D6          ║
║  3 ║     202 ║ BM-23       ║
╚════╩═════════╩═════════════╝

So, when I query a database :

SELECT count(distinct([Request])) as nbrRequests
  FROM [SRV02].[DB1].[dbo].[Project]

I get this result :

╔═════════════╗
║ NbrRequests ║
╠═════════════╣
║           2 ║
╚═════════════╝

Now, what I want is to "link"/"join" ... results from the table Users to this query, where the column Db in Users table is the name of my database, so I can get a result like this :

╔════╦═══════╦══════╦═════════════╗
║ Id ║ Name  ║  Db  ║ NbrRequests ║
╠════╬═══════╬══════╬═════════════╣
║  1 ║ Peter ║ DB1  ║           2 ║
║  2 ║ John  ║ DB16 ║           3 ║
║  3 ║ Alex  ║ DB23 ║           6 ║
╚════╩═══════╩══════╩═════════════╝

I'm trying with dynamic SQL, but no luck.

NB : Every user has only one database, and a database belong to only one user, it's one-to-one relationship

Is there any link between the requests table and the user table other than it is on a specific database? And if not, is there more than one user per database, and if yes what would be the results? — Jorge Campos
– Jorge Campos, Commented Mar 4, 2016 at 11:36
please specify table relations and how you are expecting nbrrequests as 2,3,6 — TheGameiswar
– TheGameiswar, Commented Mar 4, 2016 at 11:39
Db is the only link, and every user has only one database, and a database belong to only one user. — Hamza_L
– Hamza_L, Commented Mar 4, 2016 at 11:39
Why "no luck" with dynamic sql? What was the query you tried, and what error did you get? — Tab Alleman
– Tab Alleman, Commented Mar 4, 2016 at 14:32
@TabAlleman, No luck because I couldn't find how to join Users table to DBxx.Project table, I found a solution, you can see my answer stackoverflow.com/a/35797984/1460399 — Hamza_L
– Hamza_L, Commented Mar 4, 2016 at 14:41

Jorge Campos · Accepted Answer · 2016-03-04 11:42:42Z

3

The way you can do it is with a UNION counting every specific database table and giving it a identification for the database, like this:

SELECT u.Id, u.Name, u.Db, dbCts.nbrRequests
  FROM [Users] u INNER JOIN
      (SELECT 'DB1' as db, count(distinct([Request])) as nbrRequests
         FROM [SRV02].[DB1].[dbo].[Project]
       UNION 
       SELECT 'DB16', count(distinct([Request])) as nbrRequests
         FROM [SRV02].[DB16].[dbo].[Project]
       UNION  
       SELECT 'DB23', count(distinct([Request])) as nbrRequests
         FROM [SRV02].[DB23].[dbo].[Project]
      ) dbCts ON u.Db = dbCts.db

Don't forget to add the server and schema to the Users table I didn't because there is no such info on your question.

Also in order to do this, your connected user must have privileges on all databases.

answered Mar 4, 2016 at 11:42

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

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

2 Comments

Hamza_L Over a year ago

I dont think that this request is dynamic, I have to change it every time I add a user

Jorge Campos Over a year ago

@Hamza_L It is not Dynamic. And you have to change it every time you add a Database, given that this isn't a simple task, adding two lines in query wouldn't be that much. But it is only one approach. Feel free to chose the best answer :)

David Rushton · Accepted Answer · 2016-03-04 13:30:06Z

1

Dynamic SQL can be very tricky.

This example builds the select query from the users table. The variable @Query is incremented for each line returned by the Users table. Each row returns a query that joins the local users table to the projects table in a remote db. The results of each query are UNIONED together.

Example

-- Wil holds our dynamic query.
DECLARE @Query NVARCHAR(MAX) = '';

-- Builds our dynamic statement.
SELECT
    @Query = 
        @Query 
        + CASE WHEN LEN(@Query) > 0 THEN ' UNION ALL ' ELSE '' END
        + 'SELECT u.Id, u.Name, u.Db, COUNT(DISTINCT p.Request) AS NbrRequest '
        + 'FROM [SVR02].' + QUOTENAME(DB) + 'dbo.Project AS p INNER JOIN Users u ON u.Db= p.Db '
        + 'GROUP BY u.Id, u.Name, u.Db'
FROM
    Users
;

-- Executes the dynamic statement.
EXECUTE (@Query);

This example uses QUOTENAME to help avoid SQL injection attacks.

edited Mar 4, 2016 at 13:30

answered Mar 4, 2016 at 12:08

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

1 Comment

Hamza_L Over a year ago

why is there u.Id = p.Id ? u.Id is the id of the user, and p.id is the id of the project

Community · Accepted Answer · 2017-05-23 10:32:41Z

1

Combining these 2 answers https://stackoverflow.com/a/35795690/1460399 and https://stackoverflow.com/a/35795189/1460399, I got this solution :

DECLARE @Query NVARCHAR(MAX)= 'SELECT u.Id, u.Name, u.Db, dbCts.nbrRequests  FROM [Users] u INNER JOIN (';

DECLARE @QueryLength INT= LEN(@Query);

SELECT @Query = @Query
                +CASE WHEN LEN(@Query) > @QueryLength THEN ' UNION ' ELSE '' END
               +'SELECT '''+Db+''' as db, count(distinct(Request)) as nbrRequests FROM [SRV02].'+Db+'[Project]'
FROM Users;

SET @Query = @Query+') dbCts ON u.Db = dbCts.db';

EXECUTE (@Query);

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Mar 4, 2016 at 14:01

Hamza_L

6544 silver badges19 bronze badges

Collectives™ on Stack Overflow

dynamic sql embedded in select query

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related