CODEWITHSUNDEEP
sqlsql-server
onhax
onhax

Reputation: 339

Cross-database subquery on using database name stored in record

Is the script structure below possible?

Select 
    *, 
   (Select Count(*) 
    from [A.DatabaseName].dbo.TableA 
    where SomeID = A.SomeID) As Total 
From
    [Database1].dbo.Table1 A

The subquery above is dependent on the database name from [Database1].

Is this doable? If yes, how can this be implemented?

Upvotes: 1

Views: 289

Answers (2)

Vincent PHILIPPE
Vincent PHILIPPE

Reputation: 1181

---> Edit

I think i understand the weird thing you are tying to do.

You store some database name into a table and the want to call if from a subquery.

You have to try something like this :

CREATE DATABASE test

use test

CREATE TABLE client
(
ID IDENTITY(1,1),
[name] varchar(20)
)

INSERT INTO client
([name])
VALUES
('Jean'), ('Paul'), ('Mark'), ('Pierre');


CREATE TABLE allTable
(
NomSchema VARCHAR(200),
NomTable VARCHAR(200)
)

INSERT INTO allTable
VALUES 
(
'TEST','client'
)

IF OBJECT_ID('tempdb..#ResultA') IS NOT NULL
    DROP TABLE #ResultA
BEGIN TRAN
DECLARE @sql VARCHAR(200);
SELECT TOP(1) * INTO #resultA FROM allTable
SET @sql = 'SELECT * FROM ' + (SELECT quotename(#resultA.NomSchema) + '.dbo.' + quotename(#resultA.NomTable)  FROM #resultA)
SELECT @sql
EXEC(@sql)
DROP TABLE #resultA
COMMIT
GO

-- DROP DATABASE TEST

Upvotes: 0

DineshDB
DineshDB

Reputation: 6193

The Dynamic Query will help you.

DECLARE @DBName VARCHAR(100),@SQLQuery VARCHAR(1000)
SELECT @DBName = A.DatabaseName FROM [Database1].dbo.Table1

SELECT @SQLQuery = 'Select 
    *, 
   (Select Count(*) 
    from '+@DBName+'.dbo.TableA 
    where SomeID = A.SomeID) As Total 
From
    [Database1].dbo.Table1 A'

EXEC (@SQLQuery)

Upvotes: 1

Related Questions