Get database name in run-time

[SatSunDev]

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:

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
SELECT * from @db_name.table_name 
end

Any help is greatly appreciated. Thanks!

[ck9663]

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:

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
SELECT * from @db_name.table_name 
end

Any help is greatly appreciated. Thanks!

You're looking for the db_name() function.

-- CK

[SatSunDev]

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

[ck9663]

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:

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
SELECT * from @db_name.table_name 
end

Any help is greatly appreciated. Thanks!

Create your query dynamically. Something like..

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
 exec ('SELECT * from ' + rtrim(@db_name) + '.table_name ')
end

You can also put that query in a string var

-- CK

[SatSunDev]

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..

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
 exec ('SELECT * from ' + rtrim(@db_name) + '.table_name ')
end

You can also put that query in a string var

-- CK

[ck9663]

Depending on how you will use it, consider views and functions.

-- CK

[SatSunDev]

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".(420 00,1087). Here is my code.

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
DECLARE @results TABLE (emp_id int)
 exec('insert into ' + @results + '(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
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.

[ck9663]

Try putting the entire query into a string/varchar. Then just do a

EXEC (@sqlstring)

-- CK

[SatSunDev]

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.

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
create table dbo.#results(emp_id int)
 exec('insert into ' + #results + '(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
drop table #results
end

Try putting the entire query into a string/varchar. Then just do a

EXEC (@sqlstring)

-- CK

[SatSunDev]

sorry. That code is wrong. Here is the correct one:

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
create table dbo.#results(emp_id int)
 exec('insert into #results(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
drop table #results
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.

CREATE procedure sample_sp
          @db_name      varchar(50)
as
begin
create table dbo.#results(emp_id int)
 exec('insert into ' + #results + '(emp_id ) SELECT emp_id from ' + @db_name + '.table_name where emp_id = 1')
drop table #results
end