SQL Server: how to get a database name as a parameter in a stored procedure
EDIT
My answer assumes some things which make this approach effectively useless. Unfortunately, SO will not let me delete the answer. I recommend @MartinSmith's answer (below in this thread). I think there's still some useful information here, BUT it doesn't actually solve the original problem. Godspeed.
Original Response
There are at least two ways to do this:
Use a case/switch statement (or ,in my example, a naive
if..else
block) to compare the parameter against a list of databases, and execute a using statement based on that. This has the advantage of limiting the databases that the proc can access to a known set, rather than allowing access anything and everything that the user account has rights to.declare @dbname nvarchar(255);
set @dbname = 'db1';
if @dbname = 'db1'
use db1;
else if @dbname = 'db2'
use db2;Dynamic SQL. I HATE dynamic SQL. It's a huge security hole and almost never necessary. (to put this in perspective: In 17 years of professional development, I have never had to deploy a production system which used dynamic SQL). If you decide to go this route, limit the code that is dynamically called/created to a using statement, and a call to another stored proc do do the actual work. You can't just dynamically execute the
using
statement by itself due to scope rules.declare @sql nvarchar(255);
set @sql = 'using '+@dbname+'; exec mydatabase..do_work_proc;';
of course, in your example, you could just do
set @sql='select * from '+@dbname+'.sys.tables';
the .<schema_name>.
resolution operator allows you to query objects in a different database without using a use
statement.
There are some very, very rare circumstances in which it may be desirable to allow a sproc to use an arbitrary database. In my opinion, the only acceptable use is a code generator, or some sort of database analysis tool which cannot know the required information ahead of time.
Update Turns out you can't use
in a stored procedure, leaving dynamic SQL as the only obvious method. Still, I'd consider using
select top 100 * from db_name.dbo.table_name
rather than a use
.
SQL Server: How to use a database name as a parameter in a stored procedure?
You have to use dynamic sql:
@DBname = 'sample';
declare @sql nvarchar(max);
set @sql = '
SELECT *
FROM Table1 INNER JOIN
@DBname.table2
On Table1.key = Table2.Key';
set @sql = replace(@sql, '@DBname', @DBname);
exec sp_executesql @sql;
Databases in general -- and SQL Server in particular -- do not allow parameters to represent database names, schema names, table names, or column names.
Execute stored procedure with variable database name
So this version of your code shows includes debugging code. Remember that concatenating null values will result in a null value.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DGP_addVendor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [DGP_addVendor]
GO
CREATE PROCEDURE [dbo].[DGP_addVendor]
@dbName VARCHAR(4) = NULL ,
@NoteIndex NUMERIC(19, 5) = NULL ,
@VENDORID CHAR(15) = NULL ,
@VENDNAME CHAR(65) = NULL ,
@DEX_ROW_ID INT = NULL OUTPUT ,
@O_ErrorCode INT = NULL OUTPUT ,
@ADDRESS1 CHAR(61) = '' ,
@ADDRESS2 CHAR(61) = '' ,
@ADDRESS3 CHAR(61) = '' ,
@CITY CHAR(35) = '' ,
@STATE CHAR(29) = '' ,
@ZIPCODE CHAR(11) = '' ,
@COUNTRY CHAR(61) = '' ,
@PHNUMBR1 CHAR(21) = ''
AS
DECLARE @today DATETIME ,
@defaultDate DATETIME ,
@sql VARCHAR(MAX)
IF @DEX_ROW_ID IS NULL
SET @DEX_ROW_ID = -1
SET @today = CONVERT(DATETIME, '01/01/1900')
SET @defaultDate = CONVERT(DATETIME, '01/01/1900')
SET @sql = 'EXEC ' + @dbName + '.dbo.zDP_PM00200SI
''' + @VENDORID + '''
,''' + @VENDNAME + '''
,''' + @VENDNAME + '''
,''' + @VENDNAME + '''
,''PRIMARY''
,''PRIMARY''
,''PRIMARY''
,''PRIMARY''
,''''
,''''
,''' + @ADDRESS1 + '''
,''' + @ADDRESS2 + '''
,''' + @ADDRESS3 + '''
,''' + @CITY + '''
,''' + @STATE + '''
,''' + @ZIPCODE + '''
,''' + @COUNTRY + '''
,''' + @PHNUMBR1 + '''
,''''
,''''
,''''
,''''
,''''
,''''
,''''
,''''
,1
,''''
,''''
,''''
,0
,1
,0
,0
,''''
,0
,0
,0
,0
,0
,''''
,''''
,''''
,''''
,0
,''''
,1
,1
,1
,1
,0
,0
,1
,1
,0
,0
,0
,''''
,' + CONVERT(VARCHAR, @defaultDate) + '
,' + CONVERT(VARCHAR, @defaultDate) + '
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,' + CONVERT(VARCHAR, @NoteIndex) + '
,''''
,' + CONVERT(VARCHAR, @today) + '
,' + CONVERT(VARCHAR, @today) + '
,''''
,1
,0
,1
,''''
,''''
,0
,0
,''''
,0
,0
,0
,0
,0
,0
,' + CONVERT(VARCHAR, @defaultDate) + '
,0
,''''
,''''
,0
,9
,0
,''PRIMARY''
,' + CONVERT(VARCHAR, @DEX_ROW_ID) + ' OUT'
SET @sql = '<?query -- ' + CHAR(13) + COALESCE(@sql,'') + + CHAR(13) + ' --?>'
SELECT CONVERT(XML, @sql)
--EXEC (@sql)
SELECT @O_ErrorCode = @@ERROR
GO
DECLARE @index INT ,
@row INT ,
@error INT
SET @index = 1
EXEC [dbo].[DGP_addVendor] 'LFD', @index, 'ANT0000001',
'Anthony Quisenberry', @row OUT, @error OUT, '8506 west Rd', '', '',
'Louisville', 'KY', '40247', 'USA', ''
SELECT @row AS [@row], @error AS [@error]
How can I get the database name from which stored procedure is called?
If I remember correctly, I don't think it is possible.
There is a work-around, you can add an extra input parameter in proc2 and pass the information to it.
How to parameterize database name in SQL Server
DECLARE @oltp VARCHAR(50)
SET @oltp = 'Employee';
DECLARE @wh VARCHAR(25)
SET @wh = '_wh';
DECLARE @DatabaseName SYSNAME = @oltp + @wh;
IF NOT EXISTS ( SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
DECLARE @ErrorMsg VARCHAR(1000);
SET @ErrorMsg = QUOTENAME(@DatabaseName) + ' database does not exist';
RAISERROR(@ErrorMsg , 16 , 2);
END
ELSE
BEGIN
DECLARE @Sql NVARCHAR(MAX) = N'SELECT * FROM '
+ QUOTENAME(@DatabaseName)
+ N'..employee_sales_item;';
EXEC sys.sp_executesql @Sql;
END
Related Topics
How to View All the Metadata of Columns of a Table in Oracle Database
Using Table Variable with Sp_Executesql
How to Copy SQL Server 2008 R2 Database from One MAChine to Another
Is There Any Better Option to Apply Pagination Without Applying Offset in SQL Server
How to Add 10 Seconds in Current_Timestamp SQL ( Oracle )
Psycopg2 Equivalent of MySQLdb.Escape_String
Update Values in Struct Arrays in Bigquery
Insert/Update Tblobfield (Aka Image) Using SQL Parameters
Possible Recursive Cte Query Using Date Ranges
How to Understand How Value Converted to Date Format Implicitly
Finding Rows with Consecutive Increase in the Values of a Column
SQL Like Operator to Get the Numbers Only
Why SQL Server Go Slow When Using Variables
Ignore Certain Columns When Using Bulk Insert
Rand Not Different for Every Row in T-SQL Update
Show Create Table Tablename in SQL Server
Using Dynamic SQL to Specify a Column Name by Adding a Variable to Simple SQL Query