CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Hamza_L
Hamza_L

Reputation: 654

dynamic sql embedded in select query

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

Upvotes: 5

Views: 217

Answers (3)

Hamza_L
Hamza_L

Reputation: 654

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);

Upvotes: 1

David Rushton
David Rushton

Reputation: 5040

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.

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23381

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.

Upvotes: 3

Related Questions