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 |
|
whsbeernuts
Starting Member
11 Posts |
 Posted - 2007-08-23 : 12:01:53
|
| I have a huge question, it's for my job im doing now. I have a table with the name TWO.dbo.SVC06105. I want to be able to take the word "TWO" and put that into a variable. I know this doesn't look correct, but I want to be able to do something like this:DECLARE @GPNAME CHAR(100)SET @GPNAME = 'TWO'SELECT * FROM @GPNAME.dbo.SVC06105I know the SELECT statement isn't correct, but I hope that you guys understand what i'm trying to do through this example. BTW, I am new to this team so nice to meet you all. L8er |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-08-23 : 12:24:53
|
| Hi whsbeernuts, It would help if you explained why you want to do this. I.e. are you looking to perform the same function on an identically named table across several different databases?Have a look at dynamic sql in Books Online.An alternative (given that you're using SQL Server 2005) may be to use a synonym, but it depends on exactly what you're trying to achieve.Mark |
 |
|
|
whsbeernuts
Starting Member
11 Posts |
Posted - 2007-08-23 : 12:43:34
|
| Right now it's not going to accomplish much, but I'm doing it to set something up for later on. Right now I'm setting up the variable to a set name...SET @GPNAME = 'TWO'Like I said, I'm doing this to set up for something I'm going to do later which is this:The variable will later be a parameter which is gonna be passed in from the front-end of something we are creating. There are 2 different database names (with identical databases) accessing my stored procedures i'm creating. So eventually it will look something like this:ALTER PROC dbo.xspWOAddPart ( @GPName CHAR(100), @variable type, @anotherVariable type)ASSELECT fieldname FROM @GPName.dbo.SVC06105The SELECT statement isn't something I'm actually doing, but it's to give you an example of what I'm thinking to do. As I said, I have 'TWO' SET to the variable right now because i'm only looking to set up that part of the program for what I'm gonna do later. My boss is working with the front end of our project, and he doesn't have the params set up yet for that specific variable, so that's why I can't do it yet. So that's what I'm trying to do in a nutshell I guess. Help!!!! HAHA |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
whsbeernuts
Starting Member
11 Posts |
Posted - 2007-08-23 : 16:05:28
|
| Any more help would be appreciated as well because that example kind of helps but I don't know some of the terminology I guess.SELECT @dbname = quotename(dbname) FROM ...SELECT @sql = ' SELECT ... FROM ' + @dbname + ' .dbo.otherdbtbl ' + ' JOIN dbo.localtbl ... 'EXEC sp_executesql @sql, @params, ...I don't understand what the SELECT statements are there for, and I don't understand what quotename is, and I don't understand what any of those variables have been declared as... I don't need the JOIN part so that's ignored. Basically I need something similar to this, but is ALLLLLLL I want to know is is how to make it so I can do this....DECLARE @GPNAME CHAR(100)SET @GPNAME = 'TWO'SELECT * FROM @GPNAME.dbo.SVC06105....even though I do know you can't do "@GPNAME.dbo.SVC06105", but that's the concept of what I'm trying to do. I want the variable to be able to read "TWO" from what I have the variable set as, and then ot puts it in that SELECT statement where I have the variable.... hence SELECT * FROM TWO.dbo.SVC06105. Can anyone still help me? Thanks. |
|
|
|
|
|
|
|
|
Making part of a table name into a variable