How to dynamically assign database name in query or store procedure?

Hello,

I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?

something like

Declare @.MyDatabaseName varchar(30)

set @.MyDatabaseName = "MyDB"

SELECT * from MyDatabaseName.dbo.MyTable

Any suggestion? Please.

Thanks in advance

declare @.cmd nvarchar (2000)

declare @.MyDatabaseName nvarchar(30)

select @.MyDatabaseName = 'northwind'
select @.cmd = 'SELECT * from '+ @.MyDatabaseName +'.dbo.employees'

exec (@.cmd)

|||

It is much easier and cleaner if you simply deploy your stored procedure in each database. Using dynamic SQL has lot of security implications and you don't really simplify your code.

For a future version of SQL Server, we are looking at features that will enable you to parameterize identifiers without using dynamic SQL. And also the ability to deploy SPs in one module & resolve objects in the execution context database.

|||

Thanks Uma and Joeydj

I can't use dynamic query like Joeydj, simply because my stps are huge; but I am curious about Uma's statement about the security implication in Dynamic query. Could you elaborate a little Uma? thanks

|||

For one, you need to grant more permissions to users on your SPs if you use dynamic SQL. So you increasing the attack surface area of your database by using dynamic SQL. Additionally, if you don't protect against malicious inputs then you are vulnerable to SQL injection attacks which can compromise the database, entire server or network. Lastly, there is also the performance and maintainence aspect of dynamic SQL depending on the usage. So there are many risks in using dynamic SQL. However, for some problems in SQL Server there is no way other than using dynamic SQL (like parameterizing DDL statements, running DDLs against multiple dbs etc). But you can do these carefully by protecting your code against SQL injection attacks, using QUOTENAME for values that can be used as identifiers etc.

Below is a good link on the issues of using dynamic SQL in SQL Server. Also, search the WWW for "SQL Injection" and you will find lot of articles.

http://www.sommarskog.se/dynamic_sql.html

|||

Thank you Uma for your explaination and thanks for the link about dynamic sql. It's a great article!

appreciated.