Can you determine the DB an SP was called from?

Question

Can you determine the DB an SP was called from?

UMG Developer

SSChampion

Points: 13482
More actions
August 28, 2010 at 11:45 pm

#387683

Can you determine the DB an SP was called from?
i.e. given this code:
USE DB1;
GO
EXEC DB2.dbo.usp_Proc1;
Is there any way inside the DB2.dbo.usp_Proc1 SP to determine that the current database of the connection that called it is DB1? (I've done some Googling and couldn't find any answers.)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

As a note I did find that if I put the SP in the master DB, and name it starting with sp_ that I can do what I want to, but I really didn't want to put it in the master database.

Adiga One Orange Chip Points: 27284 More actions · Answer 2

Is there any way inside the DB2.dbo.usp_Proc1 SP to determine that the current database of the connection that called it is DB1? (I've done some Googling and couldn't find any answers.)

Not sure if I understood your requirement correctly. SELECT DB_NAME() gives the current database name. You may use this to determine if the current database in DB1.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Adiga (8/29/2010)
Not sure if I understood your requirement correctly. SELECT DB_NAME() gives the current database name. You may use this to determine if the current database in DB1.

But if you do that inside the SP in DB2, it always returns DB2, even though it was executed from DB1.

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 4

You could add a parameter to the sproc and pass the db name into it. This would of course need to be recorded somewhere.

MCITP SQL Server 2005/2008 DBA/DBD

UMG Developer SSChampion Points: 13482 More actions · Answer 5

Brian O'Leary (8/29/2010)
You could add a parameter to the sproc and pass the db name into it. This would of course need to be recorded somewhere.

Yeah, that is what I was trying to avoid. Especially since it doesn't look like you can use DB_NAME() in the call. i.e. this doesn't work: EXEC DB2.dbo.Test @DB = DB_NAME();

Which means that you have to declare a variable, set it, and then use it in the call.

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 6

Try

EXEC DB2.dbo.Test @DB = CAST(SELECT DB_NAME() AS SYSNAME);

MCITP SQL Server 2005/2008 DBA/DBD

UMG Developer SSChampion Points: 13482 More actions · Answer 7

Brian O'Leary (8/30/2010)
Try
EXEC DB2.dbo.Test @DB = CAST(SELECT DB_NAME() AS SYSNAME);

Nope, that doesn't work.

USE DB1;

GO

CREATE PROC test (@DB sysname) AS

SELECT @DB;

GO

USE DB2

EXEC DB1.dbo.test @DB = CAST(SELECT DB_NAME() AS SYSNAME);

Results in two results set being returned:

The first result set is from the test SP and contains one row with the value: CAST

The second result set is from the select statement in your execute call and contains one row with the value DB1 in a field named SYSNAME.

If I change the code to:

EXEC DB1.dbo.test @DB = CAST((SELECT DB_NAME()) AS SYSNAME);

I get this error: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'AS'.

Lowell SSC Guru Points: 323518 More actions · Answer 8

a parameter cannot be assigned inside the calling statement; just break it up into two lines::

SET @DB = DB_NAME();

EXEC DB1.dbo.test @DB

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

UMG Developer SSChampion Points: 13482 More actions · Answer 9

Lowell (8/30/2010)
a parameter cannot be assigned inside the calling statement; just break it up into two lines:

You mean three lines... (You have to declare the variable.) But that doesn't really get what I want. So far the best solution has been to put the SP in the master DB, and name it starting with SP_, that way I can use DB_NAME() inside it to determine what DB it was called from.

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 10

Oops that wasn't supposed to get posted, I've been playing around with various ways of writing it but I have to concede the rest of the world is right, you cant do it (I like to find these things out for myself the hard way 🙂 and I like a puzzle)

Lowell is right though you can do it with 2 lines, you can declare a variable and set it's values on one line:

DECLARE @Name SYSNAME = DB_NAME()

Out of curiosity why don't you want to use more than one line?

MCITP SQL Server 2005/2008 DBA/DBD

UMG Developer SSChampion Points: 13482 More actions · Answer 11

I want to make it as simple to call as possible, and it seems like a lot of extra work to pass the DB name. Since this will be for a utility SP, it is probably fine to put it in the master DB, but I really prefer to put them elsewhere.