By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,261 Members | 1,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,261 IT Pros & Developers. It's quick & easy.

Get database name in run-time

SatSunDev
P: 7
I am trying to write a stored provedure in MSSQL 2005, where it would get the name of the database as a parameter and then use this parameter in a query as follows:

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5. SELECT * from @db_name.table_name 
  6. end
Any help is greatly appreciated. Thanks!
Feb 22 '08 #1
Share this Question
Share on Google+
9 Replies


ck9663
Expert 2.5K+
P: 2,878
I am trying to write a stored provedure in MSSQL 2005, where it would get the name of the database as a parameter and then use this parameter in a query as follows:

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5. SELECT * from @db_name.table_name 
  6. end
Any help is greatly appreciated. Thanks!

You're looking for the db_name() function.

-- CK
Feb 22 '08 #2

SatSunDev
P: 7
Thanks for the response. The function db_name() returns the name of the database. In my case, I have the name of the database, passed to the stored procedure as a parameter. My question is... how can you use this name in a query. Thanks again!

You're looking for the db_name() function.

-- CK
Feb 22 '08 #3

ck9663
Expert 2.5K+
P: 2,878
I am trying to write a stored provedure in MSSQL 2005, where it would get the name of the database as a parameter and then use this parameter in a query as follows:

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5. SELECT * from @db_name.table_name 
  6. end
Any help is greatly appreciated. Thanks!
Create your query dynamically. Something like..

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5.  exec ('SELECT * from ' + rtrim(@db_name) + '.table_name ')
  6. end
  7.  
You can also put that query in a string var

-- CK
Feb 22 '08 #4

SatSunDev
P: 7
Thanks for the suggestion. I was hoping there would be a straight forward solution to this. But this looks like a good work-around. My query is actually much complex than this example... so it might look a little ugly!

Thanks a lot for your help.

Create your query dynamically. Something like..

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5.  exec ('SELECT * from ' + rtrim(@db_name) + '.table_name ')
  6. end
  7.  
You can also put that query in a string var

-- CK
Feb 22 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Depending on how you will use it, consider views and functions.

-- CK
Feb 22 '08 #6

SatSunDev
P: 7
OK. as suggested, I wrote the dynamic SQL... and is working great. But as I said earlier, my query is little more complex. I am trying to insert the results of this query into a table. But I get the error.. Must declare the table variable "@results".(42000,1087). Here is my code.

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5. DECLARE @results TABLE (emp_id int)
  6.  exec('insert into ' + @results + '(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
  7. end
QUOTE: You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

But I can't create the table inside the 'exec' statement because, in my actual code, I have the exec statement inside a WHILE loop, storing results in the table each time.

Any ideas? Thanks.
Feb 27 '08 #7

ck9663
Expert 2.5K+
P: 2,878
Try putting the entire query into a string/varchar. Then just do a

Expand|Select|Wrap|Line Numbers
  1. EXEC (@sqlstring)
-- CK
Feb 27 '08 #8

SatSunDev
P: 7
Thanks for the response.

Did that. But the problem is... the table is out of scope within that dynamic query. I instead created a temporary table. This can be used inside the query, and when its all done, I drop the table.

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5. create table dbo.#results(emp_id int)
  6.  exec('insert into ' + #results + '(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
  7. drop table #results
  8. end
Try putting the entire query into a string/varchar. Then just do a

Expand|Select|Wrap|Line Numbers
  1. EXEC (@sqlstring)
-- CK
Feb 27 '08 #9

SatSunDev
P: 7
sorry. That code is wrong. Here is the correct one:

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5. create table dbo.#results(emp_id int)
  6.  exec('insert into #results(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
  7. drop table #results
  8. end

Thanks for the response.

Did that. But the problem is... the table is out of scope within that dynamic query. I instead created a temporary table. This can be used inside the query, and when its all done, I drop the table.

Expand|Select|Wrap|Line Numbers
  1. CREATE procedure sample_sp
  2.           @db_name      varchar(50)
  3. as
  4. begin
  5. create table dbo.#results(emp_id int)
  6.  exec('insert into ' + #results + '(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
  7. drop table #results
  8. end
Feb 27 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.