How to make dynamic database name in SQL queries?

In this article, I will show you how to make dynamic database name in SQL queries. Enjoyable reading.

Sometimes, we need to make our queries dynamic. This situation usually happens when we want to change years. Let’s explain with an example.

I have 2 databases named ETRADE_2022 and ETRADE_2021.

Below is the query for the total amount of products and the total price in the invoices based on year and month.

The output is as follows:

ETRADE_2022 Output

Now I want to see the result of 2021 (actually the previous year).

For this, I need to copy the query and change the database name manually. But instead of doing it manually I will do it dynamically.

First, we will define two variables named YEAR and DBNAME. Then we’ll assign values to the variables. Then we’ll add in query.

I will show both this year and previous year queries separately.

You can see it below.

Note: We have to write the query in EXEC.

This Year Query

Previous Year Query

I just changed the year variable as year(getdate()) -1

The previous year output is as follows:

ETRADE_2021 Output

Thus, we wrote our query dynamically.

Thanks to this query, we can see the invoice data by accessing the database of the current year and the previous year without making any changes in the queries even after years.

That’s all I’m going to tell you in this article. Hope to see you in the next article.