Jtdcftul

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.)

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."

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

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

It's a better solution than using dynamic SQL.