Andras Belokosztolszki (11/1/2007)
You can create a stored procedure in the master database, and name it sp_whateverthe sp_ will make it available in all of your databases, and the context will be the calling database.
Example:
use master
GO
create proc sp_alma1 as
select db_id()
GO
use tempdb
GO
exec sp_alma1
GO
Regards,
Andras
Hi Andras,
Thanks for you reply.
I was able to get dbid() or db_name() by executing stored procedure which is created in different database.
But my requirement is to access tables in current database by executing stored procedure which is created in different database.
Here is an example/sample of my requirement.
--------------------------------------------------
use tempdb
GO
create table temptable1(col1 varchar(100), col2 int)
GO
use master
GO
create proc sp_alma1
as
select * from temptable1
GO
use tempdb
GO
exec sp_alma1
GO
--------------------------------------------------
Executing this stored procedure from different database gives me:
Server: Msg 208, Level 16, State 1, Procedure sp_alma1, Line 3
Invalid object name 'temptable1'.
Please suggest me a solution to get through this.
Thanks,
Tarish.