Identify which database a stored proc has been called from

[starsky51]

I'm trying to write a stored procedure which can identify which database it has been called from. Using the following example:

USE database1
GO
CREATE PROC dbo.uspPrintDbName
AS
PRINT DB_NAME()

if I call it from a different database:

USE database2
GO
EXEC database1.dbo.uspPrintDbName

it would print the string 'database1'.

What I would like is for the stored proc to identify which context the EXEC statement was in when it was run. (In this example, 'database2'.)
Any ideas?

 

[simian336]

Guess the obvious would be to hard code it in the sp.

But then it would not be dynamic if you moved the sp to anouther db.

Simi

 

[PWise]

how about something like this

SELECT    name
FROM         master.dbo.sysprocesses p
inner join master.dbo.sysdatabases db
on p.dbid = db.dbid
where spid=@@spid

 

[starsky51]

PWise. I had high hopes for your suggestion but, annoyingly, the dbid value gets updated when the session executes the sproc. I've had a hunt through some of the other sys tables, but none of them seem to hold the info I need.

It looks like I'll have to set up the sproc with an extra @dbname parameter and get the user to enter it manually.

Thanks to you both for your suggestions.