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?
Pinhole Camera with Instant Film
Why would a flight no longer considered airworthy be redirected like this?
SQL Server Primary Login Restrictions
Does the statement `int val = (++i > ++j) ? ++i : ++j;` invoke undefined behavior?
Make a transparent 448*448 image
Is it true that real estate prices mainly go up?
Co-worker team leader wants to inject his friend's awful software into our development. What should I say to our common boss?
Sword in the Stone story where the sword was held in place by electromagnets
Ban on all campaign finance?
How could a female member of a species produce eggs unto death?
Know when to turn notes upside-down(eighth notes, sixteen notes, etc.)
I need to drive a 7/16" nut but am unsure how to use the socket I bought for my screwdriver
Instead of Universal Basic Income, why not Universal Basic NEEDS?
How to make healing in an exploration game interesting
How to explain that I do not want to visit a country due to personal safety concern?
Does splitting a potentially monolithic application into several smaller ones help prevent bugs?
An Accountant Seeks the Help of a Mathematician
Replacing Windows 7 security updates with anti-virus?
Can the damage from a Talisman of Pure Good (or Ultimate Evil) be non-lethal?
What is the greatest age difference between a married couple in Tanach?
What is a good source for large tables on the properties of water?
In-house repeater?
Bash: What does "masking return values" mean?
The use of "touch" and "touch on" in context
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?
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
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
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
6 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
6 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.
5 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
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
sql-server sql-server-2008-r2 stored-procedures dynamic-sql
asked 7 hours ago
J.D.J.D.
512312
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
6 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
6 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.
5 hours ago
add a comment |
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
6 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
6 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.
5 hours ago
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
6 hours ago
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
6 hours ago
1
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
6 hours ago
@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
6 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.
5 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.
5 hours ago
add a comment |
3 Answers
3
active
oldest
votes
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."
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.
3 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
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.
5 hours ago
I am not sure what the role of master is, here? db_name() returns the current database name (use test
) anyway?
– eckes
22 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.
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.
5 hours ago
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.
– Aaron Bertrand♦
3 hours ago
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.
– Dan
3 hours 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
);
);
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
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
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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."
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.
3 hours ago
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."
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.
3 hours ago
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."
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."
edited 3 hours ago
answered 4 hours ago
Aaron Bertrand♦Aaron Bertrand
152k18294490
152k18294490
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.
3 hours ago
add a comment |
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.
3 hours ago
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.
3 hours ago
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.
3 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
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.
5 hours ago
I am not sure what the role of master is, here? db_name() returns the current database name (use test
) anyway?
– eckes
22 mins 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
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.
5 hours ago
I am not sure what the role of master is, here? db_name() returns the current database name (use test
) anyway?
– eckes
22 mins 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
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
edited 6 hours ago
answered 6 hours ago
Randi VertongenRandi Vertongen
3,569822
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.
5 hours ago
I am not sure what the role of master is, here? db_name() returns the current database name (use test
) anyway?
– eckes
22 mins ago
add a comment |
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.
5 hours ago
I am not sure what the role of master is, here? db_name() returns the current database name (use test
) anyway?
– eckes
22 mins ago
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.
5 hours ago
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.
5 hours ago
I am not sure what the role of master is, here? db_name() returns the current database name (
use test
) anyway?– eckes
22 mins ago
I am not sure what the role of master is, here? db_name() returns the current database name (
use test
) anyway?– eckes
22 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.
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.
5 hours ago
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.
– Aaron Bertrand♦
3 hours ago
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.
– Dan
3 hours 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.
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.
5 hours ago
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.
– Aaron Bertrand♦
3 hours ago
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.
– Dan
3 hours 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.
Why don't you create your own SQL Server System Stored Procedures?
It's a better solution than using dynamic SQL.
answered 6 hours ago
DanDan
644416
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.
5 hours ago
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.
– Aaron Bertrand♦
3 hours ago
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.
– Dan
3 hours ago
add a comment |
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.
5 hours ago
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.
– Aaron Bertrand♦
3 hours ago
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.
– Dan
3 hours ago
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.
5 hours ago
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.
5 hours ago
1
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.
– Aaron Bertrand♦
3 hours ago
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.
– Aaron Bertrand♦
3 hours ago
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.
– Dan
3 hours ago
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.
– Dan
3 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
6 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
6 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.
5 hours ago