Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrityukc
Starting Member
1 Post |
 Posted - 2012-06-29 : 11:02:46
|
| I have a stored procedure in database A that executes multiple queries on several tables in database B. I want to declare the name of the database B in a variable in the stored procedure and use that variable to execute queries on different tables. I want to do this so that any change in the name of the database in future would not need to change the queries at several places.I am using this code:declare @a nvarchar(100)select @a='databaseName'select * from @a.tablenamebut its not working.Kindly let me know of any solution. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-29 : 12:37:38
|
Changing the name of a production database should be a rare and very undesirable event, at least in my limited experience as a database developer. However, designing your programs to account for such a change takes a lot of effort, adds security risks, causes reduced reliability etc. So I would recommend against programming for this scenario.If you want to do simple selects you can use dynamic SQL. For your example, it would be this:declare @a nvarchar(100)select @a='databaseName'DECLARE @sql NVARCHAR(4000);SET @sql = 'select * from ' + @a + '.dbo.tablename'exec sp_executesql @sql; But, it has so many undesirable effects - risk of SQL injection attacks, if your query is more than 4,000 characters long it would not work, etc.All that said, your business requirements may demand this type of name change, and you may have no choice. So I am not judging what you are trying to do, just offering my (hopefully constructive) opinions. |
 |
|
|
|
|
|
How to use database as variable name