3

Weird question?...maybe, but I have a need. :)

I have a stored procedure that I want to use universally in any database. The stored procedure generates some dynamic SQL and then executes that SQL in a database that is passed in as one of the parameters in this procedure.

BUT I want to make the database parameter optional and when no database name is passed in, I want the dynamic SQL to execute within the same database that the procedure itself was called from. (Please keep in mind this procedure could be executed across databases and not within the same database that the procedure itself lives in.)

3
  • Do you need dynamic SQL? Is something stopping you from doing like: IF @dbname IS NULL then @dbname = (SELECT dbname()) or you mean the calling procedure itself?
    – Jacob H
    Mar 14 '19 at 18:03
  • 2
    @JacobH that unfortunately does not work when calling a stored proc from a different database, as it returns the dbname where the stored proc resides in. Mar 14 '19 at 18:43
  • Right, I want when the procedure executes, a way for the procedure to be able to determine which database the query that executed it was executed from. For example if my procedure lives in database A, and I execute it from database B, then I want the procedure that lives in database A to be able to determine the query that executed it came from database B.
    – J.D.
    Mar 14 '19 at 19:14
6

You can easily tell the dynamic SQL execute in a specific database by dynamically building a [database].sys.sp_executesql command:

USE your_database;
GO

CREATE PROCEDURE dbo.DatabaseNameOptional
    @db sysname = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(); /* other stuff */'

  DECLARE @exec nvarchar(770) = COALESCE(@db, DB_NAME())
    + N'.sys.sp_executesql';

  -- alternatively, just leave DB_NAME() out of it:

  --DECLARE @exec nvarchar(770) = COALESCE(@db, N'')
  --  + N'sys.sp_executesql';

  EXEC @exec @sql;
END
GO

Try it out:

USE your_database;
GO

EXEC dbo.DatabaseNameOptional;
GO  -- output = your_database

EXEC dbo.DatabaseNameOptional @db = N'master';
GO  -- output = master

USE tempdb;
GO

EXEC your_database.dbo.DatabaseNameOptional;
GO  -- output = your_database

EXEC your_database.dbo.DatabaseNameOptional @db = N'master';
GO  -- output = master

In the execution context of the procedure, though, no, I don't think there's any way to determine where the call originated from (or to run in that context). That's the benefit of using a system-marked procedure in master - if that's the functionality you want, you need to decide if "putting objects in master" is ickier than "not getting what I want."

8
  • Thanks! I'm good on building / executing the dynamic SQL end, but I'm sad there isn't a better way to determine the database which the stored procedure query was executed from. Perhaps it would be pretty janky, but what about going by one of the tables or DMVs that cache query execution stats? (I guess you would need to know the user who executed it too to not mix up concurrent executions.)
    – J.D.
    Mar 14 '19 at 21:41
  • I'm confused. I appreciate the answer does indeed do useful stuff! But it seems as if it doesn't (fully?) answer the question. i.e. It doesn't work out the name of the calling DB if no parameter is passed in. Instead it defaults to the name of the DB in which the procedure lives - which I think is exactly what the OP wanted to avoid? (I'm not just commenting for the sake of it - I want to know how to do this too!)
    – MikeBeaton
    Mar 10 '20 at 12:25
  • 2
    @MikeBeaton If you put the stored procedure in master, prefix it with sp_, and mark it as a system object, then calling it from any database will make it execute in the context of the calling database. There is no way (again, that I know of) to mimic that behavior in a stored procedure in any user database. Mar 10 '20 at 14:22
  • 1
    Thank you @AaronBertrand . Since you last replied, I've now written up the option you've just suggested (which is also in the two other answers below) with more detail, as an additional answer. I suggested writing an optional, thin wrapper over the original proc. I do think (based on my reading of the OP's question and their subsequent comments) that mimicing that behaviour in an SP in a user database is exactly that the OP was trying to do. And I do agree with you, there's no sign that it can be done, unfortunately.
    – MikeBeaton
    Mar 10 '20 at 14:28
  • 2
    @MikeBeaton Yep. I didn't include that option in my original answer because it already existed in other answers. I was providing a different option in case the OP didn't want to put an object in master (or couldn't, due to policy, permissions, or using Azure SQL Database). Mar 10 '20 at 14:32
4

I don't like creating procedures in master, but if you put your procedure in the master database and add the SP_ prefix like sp_dynamicproc, you could call it from inside your own user database and get the correct db_name() parameter.

Source

An example

USE master  
GO
CREATE PROCEDURE dbo.sp_dynamicproc
(@dbname nvarchar(255) = NULL)
as
BEGIN

DECLARE @SQL NVARCHAR(MAX)

IF @dbname IS NULL 
BEGIN
SET @dbname = (SELECT db_name());
END
SELECT db_name()
SET @SQL = 'SELECT * FROM '+QUOTENAME(@dbname)+'.INFORMATION_SCHEMA.COLUMNS ;';
EXEC(@SQL);

END

Calling the proc

USE test
GO
exec dbo.sp_dynamicproc;

Result

enter image description here

4
  • Cool, this is helpful, +1 for that. But I'm with ya, I'd prefer to not create objects in the master database, and I have a dedicated database this procedure lives in with other similar types of functions.
    – J.D.
    Mar 14 '19 at 19:17
  • I am not sure what the role of master is, here? db_name() returns the current database name (use test) anyway?
    – eckes
    Mar 15 '19 at 0:38
  • @eckes Yes but the procedure is only defined in master, we can call it from our any user db without specifying any other database name. If we created the same proc in say test2, we cannot call it from test without specifying exec test2.dbo.sp_dynamicproc, where db_name() would return test2` Mar 15 '19 at 7:42
  • This gets the name of the calling DB, which is important and useful to know. It would be nice to put just this limited functionality (identify root DB) into master, but even that doesn't work! If called from within a utility procedure, this shows the DB which the utility procedure lives in, not the DB which called the utility procedure. This may well be as expected, but it means that this can't be used to solve the original problem unless the root procedure to be called becomes one in master, not one in the 'utility' database - as I think the OP wanted and as I'm still hoping/trying to do!
    – MikeBeaton
    Mar 10 '20 at 12:44
0

Why don't you create your own SQL Server System Stored Procedures?

It's a better solution than using dynamic SQL.

3
  • Dynamic SQL will be occurring regardless, because I need to change my SQL statements that are executed based on the parameters inputted into the procedure. I gave you the +1 though because your answer is similarly helpful as @Randi's but I'd prefer to not create objects in the master database.
    – J.D.
    Mar 14 '19 at 19:20
  • 1
    Why is dynamic SQL bad? It's like a lot of things - it can be dangerous in certain scenarios, if you choose to use it without any research, but it shouldn't be written off just as a matter of course. It can accomplish a lot of really powerful things that you wouldn't be able to do otherwise. Mar 14 '19 at 21:21
  • Agreed, but I'm not saying dynamic SQL is bad. It's really powerful and I do like it actually. I use it in my favor to improve performance in difference situations, and to do whatever I need to customize some routines. When I first suggested, it seemed that it would be a "static stored procedure" or something like that. Using System Stored procedures he would be able to run something like: EXEC DB1.dbo.SP_whatever , EXEC DB2.dbo.SP_whatever. It would be pretty easy to know from which database it comes from. Mar 14 '19 at 21:55
0

Say we have a database Utils with a procedure MyUtil and we do:

USE MyDatabase
GO
EXEC Utils.dbo.MyUtil
GO

then as per the original question we would like Utils.dbo.MyUtil to perform some utility operations inside MyDatabase, not inside Utils.

There seems to be no direct way to do exactly this. Accessing DB_NAME(), sys.sysprocesses or sys.dm_exec_sessions within Utils.dbo.MyUtil will always identify the current database as Utils regardless of where Utils.dbo.MyUtil was called from. On the other hand syslogins and sys.sql_logins return the login database, not the current database (e.g. as selected with USE xyz).

The problem is not that Utils.dbo.MyUtil can't do it's work, it's that it can't automatically work out where to do its work (i.e. it can't tell where it was called from), as we'd ideally like it do.

One option (still less than ideal, because it involves modifying master; this builds on and adds to the answers by @RandiVertongen and @DaniloBraga) is to provide a thin wrapper procedure in the master database:

USE master
GO
CREATE PROCEDURE sp_MyUtil
    {...other params}
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @default_db_name sysname = DB_NAME()

    EXEC Utils.dbo.MyUtil {...other params,} @default_db_name = @default_db_name
END
GO

USE Utils
GO
ALTER PROCEDURE MyUtil
    {...other params,}
    @default_db_name sysname = null
AS
BEGIN
    SET NOCOUNT ON;

    {...do stuff which uses @default_db_name to identify the calling DB, this is set correctly and passed automatically when sp_MyUtil is used}
END
GO

No you can call:

USE MyDatabase
GO
EXEC sp_MyUtil
GO

this ends up executing Utils.dbo.MyUtil, but now with enough information available to it for it to do its work in MyDatabase.

Bear in mind the strong recommendation (for security reasons) never to install modifications to the master database on a production server. Because these master procedures are only a convenience to enable the user to type less, you could always install the Utils database without the master wrapper procedures on the production server... if you needed to.

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.