Code:
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
SatSunDev
Last Activity: Mar 28 '08, 07:27 PM
Joined: Feb 22 '08
Location:
-
sorry. That code is wrong. Here is the correct one:
... -
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.
...Code:CREATE procedure sample_sp @db_name varchar(50) as begin create table dbo.#results(emp_id int) exec('insert into ' + #results + '(emp_id ) SELECTLeave a comment:
-
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.
...Code:CREATE procedure sample_sp @db_name varchar(50) as begin DECLARE @results TABLE (emp_id int)Leave a comment:
-
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....Leave a comment:
-
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!...Leave a comment:
-
Get database name in run-time
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:
Any help is greatly appreciated. Thanks!Code:CREATE procedure sample_sp @db_name varchar(50) as begin SELECT * from @db_name.table_name end -
I am also looking for something like that. Do you happen to find a solution for your problem? I am working in MSSQL 2005....Leave a comment:
No activity results to display
Leave a comment: