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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 USING VARIABLE FOR DATABASE NAME

Author  Topic 

doco
Yak Posting Veteran

77 Posts

Posted - 2008-02-18 : 10:20:31
I often write script for four other entities that use the same databases with the difference being the names of the databases across the four entities. Each entity has two different databases.

EG. First database: Prefix[entity_name]Common
Second database: Prefix[entity_name]Main

The only thing that changes in these two databases across entities is the [entity_name]. It is quite common to refer to both databases in script; and it is quite common for an individual script to become hundreds of lines with scores of references to the databases.

Yes, it is fairly simple to use a text editor to replace all database names with pertinent names. What I am curious is there a way to use a variable to identify the databases in one place in the beginning of the script? Concatenation of @database_name + '..table_name' throws an error.

TIA

Education is what you have after you've forgotten everything you learned in school

321dutch
Starting Member

2 Posts

Posted - 2008-02-18 : 11:04:33
You must use dynamic sql in order to use a variable for either a db or column name.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 11:09:09
This can be done by means of dynamic sql. use a variable to store dbname name and use this variable to built sql string like

SELECT @Sql ='SELECT fields FROM ' + @dbname +'.Table'

then use EXEC() or sp_executesql to execute the sql string.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 11:33:09
In case if you cant avoid dynamic sql www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

doco
Yak Posting Veteran

77 Posts

Posted - 2008-02-18 : 16:28:27
Thanks guys.

I am very new to TSQL but have a programming background. It just seems a 'reasonable' thing to be able to do, I guess not.



Education is what you have after you've forgotten everything you learned in school
Go to Top of Page
   

- Advertisement -