question

MaheshMadhusanka-2060 avatar image
0 Votes"
MaheshMadhusanka-2060 asked ·

How to add a Dynamic Database name to SQL Query using the column data

Hi Team,

Currently we have a requirement t get a particular table columns but that same name table available on the different table, its mean table name same and Database name Dynamic but Dynamic data table last two digit available on the previous table column (SM_DB), Currently we have a 38 databases (Dynamic Database name start from "ODYSSEYEXKPRD_SD001" to 'ODYSSEYEXKPRD_SD038' ) so we can get a last every database name last 2 digit from previous table ('StorageMain' table column name 'SM_DB' so how can we get select the relevant database name using previous table column (SM_DB) Value this Colum value parse as a 1 to 38. For your reference here with attached Sample SQL Query, Could you please check and advise on it?




75105-image.png


sql-server-generalsql-server-transact-sql
image.png (52.0 KiB)
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

Why are there 38 databases in the first place?

One option is to set up a inline-table valued function with the database name as a parameter:

CREATE FUNCTION OdyssesTable(@dbno int) AS RETURNS TABLE
RETURN (
     SELECT ...
     FROM   ODYSSEYEXKPRD_SD001
     WHERE @dbno = 1
     UNION ALL 
     SELECT ...
     FROM   ODYSSEYEXKPRD_SD002
     WHERE @dbno = 2
     ...
)

If you and a new database you will need to change the function.

One problem is that the user running a SELECT that calls this function may not have permission to all 38 databases. I believe this works out if there already is a plan for the database in cache, but compilation will fail.

The other alternative is dynamic SQL, which will lead to that the code is littered and more difficult to read and maintain. I have written about dynamic SQL in this article: The Curse and Blessings of Dynamic SQL.

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog Thank you for above answer, But here issue is we cant say its a 38 its can be a higher number with the future so, Can we create a Synamic Database name query base on the SM_DB Colum value?

inadditionalyy Can t we use below type solution?

SELECT top 10*
FROM JobShipment
Left join StorageMain on SM_ParentFK = JS_PK
where [ODYSSEYEXKPRD_SD0+SM_DB].[dbo].[StorageDocs].SC_SM = SM_PK;

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog MaheshMadhusanka-2060 ·

Thank you for above answer, But here issue is we cant say its a 38 its can be a higher number with the future so

Yes, as I said, when you add a new database you will need to update the function. Presumably, you already have procedures in place for adding a new database, so extending the function would be to those procedures.

Can t we use below type solution?

SELECT top 10*
FROM JobShipment
Left join StorageMain on SM_ParentFK = JS_PK
where [ODYSSEYEXKPRD_SD0+SM_DB].[dbo].[StorageDocs].SC_SM = SM_PK;

No. An SQL query is static in what tables and databases it accesses. You can build an SQL string dynamically, and that is known as dynamic SQL, see the link in my original post.

You did not answer the question why there are these many databases and why you want to run that type of query. And that is not an unimportant one, since this is your overall problem.




0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

The same confusion, why there are 38 or more databases to store a single table. In addition, if top is not used with order by, it is actually meaningless.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In addition, if top is not used with order by, it is actually meaningless.

It's non-deterministic, but it is necessarily not meaningless, depending on what you want to do.

In this case, I think that Mahesh wanted to show us some sample rows, so TOP 5 without ORDER BY works fine.

0 Votes 0 ·