Author |
Topic |
Arithmomaniac
Yak Posting Veteran
66 Posts |
Posted - 2008-07-30 : 12:33:42
|
How do you get a dynamic USE statement to work?Example:DECLARE @Name varchar(50);SET @Name = '[Database]';EXEC('USE ' + @Name);GOSELECT * FROMN dbo.Table The first batch gives no problems, but the second not work, sine there is no master.dbo.Table. Why not?Replacing Line 3 with EXEC Sp_ExecuteSQL N'USE @X', N'@X varchar(50)', @X = @Name;says Msg 137, Level 15, State 2, Line 1Must declare the scalar variable "@Name".Why doesn't that work? Will anything along these lines work? Thanks, Arithmomaniac---------Ignorance may be bliss, but knowledge is thrill. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-30 : 12:46:53
|
It doesn't work that way, which I believe we've mentioned before. You'd have to do 'SELECT * FROM ' + @dbName + 'dbo.Table'. Please show us a real example of what you are trying to do. Based upon your recent questions, I think you are going to need a big redesign.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-30 : 12:56:07
|
...or execute the USE and the SELECT as a single dynamic string. Exec operates within its own scope, so once it completes you pop back into the calling scope as if nothing had changed.e4 d5 xd5 Nf6 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
Arithmomaniac
Yak Posting Veteran
66 Posts |
Posted - 2008-07-30 : 13:11:45
|
(written before blindman posted)Really, the USE problem is a problem of its own; but here goes:I need to add 2 columns to a table that indicate whetherColumn1: The Phone Number in ThisTable.Phone was found in DB2.Table1.Column1, DB2.Table1.Column2, or DB2.Table2.Column1.Column2: The Fax Number in ThisTable.Phone was found in DB2.Table1.Column3, or DB2.Table2.Column2.Neither no column in either database has unique numbers (meaning, in every column, something could appear twice).Also, the phone numbers have to be "cleaned" for comparison so that they are 10 digits long and have no (, ), or - characters in them.My rough path wasInsert columns into tableCreate PhoneNumber cleaner functionCreate Sproc that can figure out whether the row member for ThisTable.[Pick any column] was found anywhere in DB2.[Pick any Table].[Pick any column], and update the new columns with this information if we don't already know we have a match (meaning, update statement does not run when the result in the column to update is already 1).Run the sproc five times with different parameters.The query works well, but then I was asked to make this query adaptable for different database names by calling out the database names at the top of the script as variables, and then using those variables throughout the code.I figured out how to pass the value of DB2: Dump @DB2 into ##TableNames, and then create @DB2 again from ##TableNames after every batch. But if the query starts in master, how do I get it to switch to @ThisDB?---------Ignorance may be bliss, but knowledge is thrill. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
Arithmomaniac
Yak Posting Veteran
66 Posts |
Posted - 2008-07-30 : 13:23:24
|
quote: Originally posted by tkizer But why do you have different databases and why aren't the databases normalized?
DB1 is the registration info for the software my firm makes.DB2 is the underlying data for an old implementation of GoldMine.GoldMine's schema cannot be modified or hooked up directry to an external database.---------Ignorance may be bliss, but knowledge is thrill. |
|
|
Arithmomaniac
Yak Posting Veteran
66 Posts |
Posted - 2008-07-30 : 14:08:31
|
I settled for the first proposed idea of running in default database and referencing all the tables.---------Ignorance may be bliss, but knowledge is thrill. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-31 : 05:06:39
|
www.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|