Ttyjukl

Bastion server: use TCP forwarding VS placing private key on server

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

Why does Deadpool say "You're welcome, Canada," after shooting Ryan Reynolds in the end credits?

Why must traveling waves have the same amplitude to form a standing wave?

2D counterpart of std::array in C++17

How is the Swiss post e-voting system supposed to work, and how was it wrong?

Happy pi day, everyone!

Be in awe of my brilliance!

Making a sword in the stone, in a medieval world without magic

Employee lack of ownership

Where is the 1/8 CR apprentice in Volo's Guide to Monsters?

It's a yearly task, alright

Could the Saturn V actually have launched astronauts around Venus?

/bin/ls output does not match manpage

Schematic conventions for different supply rails

Am I not good enough for you?

What is IP squat space

Welcoming 2019 Pi day: How to draw the letter π?

Why are the outputs of printf and std::cout different

Does the statement `int val = (++i > ++j) ? ++i : ++j;` invoke undefined behavior?

Sword in the Stone story where the sword was held in place by electromagnets

Theorems like the Lovász Local Lemma?

Ban on all campaign finance?

PTIJ: Who should pay for Uber rides: the child or the parent?

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receiveHow to log within a stored procedure that is encapsulated within a transactionHow to use same stored procedure for different tables and avoid using dynamic sqlSSRS - ERROR NUMBER:7357 while passing multiple value parameters using comma delimited string to stored procedureHow to write stored procedure use SP_HELPTEXT for getting information of objects belong to another databaseWhy always throwing error “Must declare scalar variable” while executing stored procedure?Multiple disparate functions within stored procedureHow properly call stored procedure from EXEC(SQL)?Stored Procedure Asking for Input While Processinghow to add option(recompile) within this stored procedure?

2

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

sql-server sql-server-2008-r2 stored-procedures dynamic-sql

share|improve this question

asked 3 hours ago

J.D.J.D.

512312

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @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.
  
  – Randi Vertongen
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  

add a comment | 

2

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

sql-server sql-server-2008-r2 stored-procedures dynamic-sql

share|improve this question

asked 3 hours ago

J.D.J.D.

512312

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @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.
  
  – Randi Vertongen
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  

add a comment | 

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

sql-server sql-server-2008-r2 stored-procedures dynamic-sql

share|improve this question

asked 3 hours ago

J.D.J.D.

512312

share|improve this question

asked 3 hours ago

J.D.J.D.

512312

share|improve this question

asked 3 hours ago

J.D.J.D.

512312

asked 3 hours ago

J.D.J.D.

512312

asked 3 hours ago

J.D.J.D.

512312

3 Answers
3

active

oldest

votes

3

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

share|improve this answer

edited 5 mins ago

answered 40 mins ago

Aaron Bertrand♦Aaron Bertrand

152k18294490

add a comment | 

1

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

share|improve this answer

edited 2 hours ago

answered 2 hours ago

Randi VertongenRandi Vertongen

3,569822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  

add a comment | 

0

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

It's a better solution than using dynamic SQL.

share|improve this answer

answered 3 hours ago

DanDan

644416

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Why is dynamic SQL bad? It's like a lot of things - it can be dangerous in certain scenarios 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.
  
  – Aaron Bertrand♦
  2 mins ago
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232169%2fhow-to-get-the-name-of-the-database-a-stored-procedure-is-executed-in-within-tha%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3

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

share|improve this answer

edited 5 mins ago

answered 40 mins ago

Aaron Bertrand♦Aaron Bertrand

152k18294490

add a comment | 

3

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

share|improve this answer

edited 5 mins ago

answered 40 mins ago

Aaron Bertrand♦Aaron Bertrand

152k18294490

add a comment | 

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

share|improve this answer

edited 5 mins ago

answered 40 mins ago

Aaron Bertrand♦Aaron Bertrand

152k18294490

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

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

share|improve this answer

edited 5 mins ago

answered 40 mins ago

Aaron Bertrand♦Aaron Bertrand

152k18294490

answered 40 mins ago

Aaron Bertrand♦Aaron Bertrand

152k18294490

answered 40 mins ago

Aaron Bertrand♦Aaron Bertrand

152k18294490

1

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

share|improve this answer

edited 2 hours ago

answered 2 hours ago

Randi VertongenRandi Vertongen

3,569822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  

add a comment | 

1

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

share|improve this answer

edited 2 hours ago

answered 2 hours ago

Randi VertongenRandi Vertongen

3,569822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  

add a comment | 

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

share|improve this answer

edited 2 hours ago

answered 2 hours ago

Randi VertongenRandi Vertongen

3,569822

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

USE test

GO

exec dbo.sp_dynamicproc;

share|improve this answer

edited 2 hours ago

answered 2 hours ago

Randi VertongenRandi Vertongen

3,569822

answered 2 hours ago

Randi VertongenRandi Vertongen

3,569822

answered 2 hours ago

Randi VertongenRandi Vertongen

3,569822

0

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

It's a better solution than using dynamic SQL.

share|improve this answer

answered 3 hours ago

DanDan

644416

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Why is dynamic SQL bad? It's like a lot of things - it can be dangerous in certain scenarios 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.
  
  – Aaron Bertrand♦
  2 mins ago
  
  
  

  
  
  
  

add a comment | 

0

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

It's a better solution than using dynamic SQL.

share|improve this answer

answered 3 hours ago

DanDan

644416

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Why is dynamic SQL bad? It's like a lot of things - it can be dangerous in certain scenarios 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.
  
  – Aaron Bertrand♦
  2 mins ago
  
  
  

  
  
  
  

add a comment | 

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

It's a better solution than using dynamic SQL.

share|improve this answer

answered 3 hours ago

DanDan

644416

share|improve this answer

answered 3 hours ago

DanDan

644416

answered 3 hours ago

DanDan

644416

answered 3 hours ago

DanDan

644416