8

Suppose I have a stored procedure that is duplicated, with some modifications, in several databases. And I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

Is there a way to retrieve the full path (..) or otherwise retrieve the database in which the stored procedure is stored, rather than the current database?

  • I did not know that the proc executes in the database context where it lives and I want to make sure that running it in while in another database would not affect the database you were in. – Jim Clark Apr 26 at 15:54
13

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure. In particular,

For static SQL in a stored procedure:

  • Unqualified object names will resolve relative to the schema containing the stored procedure.

  • Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

  • Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).

  • Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.

4

Here is a quick example that I put together showing common functions used to get close to what you are looking for.

/** Create a procedure in master to demonstrate
    DB_NAME()
    OBJECT_SCHEMA_NAME()
    OBJECT_NAME()
    @@PROCID
**/
USE [master]
GO

CREATE OR ALTER PROCEDURE dbo.uspTestMe
AS
BEGIN

    PRINT 'Database: ' + DB_NAME()
    PRINT 'Schema Name: ' + OBJECT_SCHEMA_NAME(@@PROCID)
    PRINT 'Procedure Name: ' + OBJECT_NAME(@@PROCID)

END

GO

/** CHANGE Context to TempDB
    Execute procedure in master
    **/
USE [tempdb]
GO

EXEC master.dbo.uspTestMe 

GO

/** Cleanup in master **/
USE [master]
GO

DROP PROCEDURE IF EXISTS dbo.uspTestMe 
0

Adding to David Browne's excellent answer:

I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

You're in luck because this is how regular / permanent, non-system stored procedures already work this way, and you actually need to go out of your way to get objects to operate in the current DB. This is how functions work as well, it's just that with stored procedures, you have options — marking them as "system stored procedures" or creating temporary stored procedures — that you don't have with functions / views / triggers / etc.

Because built-in functions behave slightly differently than static SQL in temporary stored procedures, the following example uses a non-temporary table to reference in both a UDF and Inline-TVF. True, the following example does not actually test temporary stored procedures, but the reason I used the non-temporary table is because since we have one instance of differing behavior, we need to make sure that such behavior does not happen here. In the following example, if either type of function were aware of the "current" database, then the reference to the non-temporary table would cause an error.

SETUP

USE [tempdb];

CREATE IF NOT EXISTS TABLE dbo.InTempDB (Col1 INT);
INSERT INTO dbo.InTempDB ([Col1]) VALUES (999);

GO
CREATE 
OR ALTER -- comment out if using SQL Server < 2017
FUNCTION dbo.GetDbNameUDF()
RETURNS SYSNAME
AS
BEGIN
  DECLARE @DoNothing INT;
  SELECT @DoNothing = [Col1] FROM dbo.InTempDB;

  RETURN DB_NAME();
END;

GO
CREATE
OR ALTER -- comment out if using SQL Server < 2017
FUNCTION dbo.GetDbNameITVF()
RETURNS TABLE
AS
RETURN
  SELECT DB_NAME() AS [DbName],
         tmp.[Col1]
  FROM   dbo.InTempDB tmp;

GO

TEST

USE [model];

SELECT DB_NAME() AS [CurrentDB],
       tempdb.dbo.GetDbNameUDF() AS [DbNameFromUDF];
-- CurrentDB    DbNameFromUDF
-- model        tempdb


SELECT DB_NAME() AS [CurrentDB],
       *
FROM   tempdb.dbo.GetDbNameITVF();
-- CurrentDB    DbName    Col1
-- model        tempdb    999


/* -- clean-up
DROP TABLE dbo.InTempDB;
DROP FUNCTION dbo.GetDbNameUDF;
DROP FUNCTION dbo.GetDbNameITVF;
*/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.