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 2005 Forums
 Transact-SQL (2005)
 Dynamic USE Statements

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);
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

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 13:08:40
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 - 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 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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 13:16:35
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 - 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.
Go to Top of Page

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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 05:06:39
www.sommarskog.se/dynamic_sql.html

Madhivanan

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

- Advertisement -