SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Dynamic USE Statements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/30/2008 :  12:33:42  Show Profile  Reply with Quote
How do you get a dynamic USE statement to work?
Example:

DECLARE @Name varchar(50);
SET @Name = '[Database]';
EXEC('USE ' + @Name);
GO
SELECT * 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 1
Must 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

USA
36942 Posts

Posted - 07/30/2008 :  12:46:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 07/30/2008 12:49:29
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 07/30/2008 :  12:56:07  Show Profile  Reply with Quote
...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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36942 Posts

Posted - 07/30/2008 :  13:08:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
You've got to see his other threads.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/30/2008 :  13:11:45  Show Profile  Reply with Quote
(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 whether
Column1: 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 was

Insert columns into table
Create PhoneNumber cleaner function
Create 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.

Edited by - Arithmomaniac on 07/30/2008 13:13:35
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36942 Posts

Posted - 07/30/2008 :  13:16:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
But why do you have different databases and why aren't the databases normalized?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/30/2008 :  13:23:24  Show Profile  Reply with Quote
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.

Edited by - Arithmomaniac on 07/30/2008 13:24:06
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/30/2008 :  14:08:31  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/31/2008 :  05:06:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000