英文:
Set DB name in SQL Server stored procedure so that it can be set at the beginning of the script and not for every update statement
问题
I've created a stored procedure which should overwrite/update some column values in a database. The reason for that is that it's a backup from PROD to our TEST environment and I want to overwrite path/folder locations to another location as the PROD environment.
The database name can be different every time and I would like to set it only once in my stored procedure, just to prevent that I have to set it for every update statement.
So I did something like here below, but this is not accepted by SSMS:
DECLARE @DBNAME NVARCHAR(50)
SET @DBNAME = 'TEST'
UPDATE @DBNAME.[DBO].[SomeTable]
SET [code] = 'SomeCode'
UPDATE @DBNAME.[DBO].[AnotherTable]
SET [code] = 'AnotherCode'
What am I doing wrong here? Is it because I'm declaring it as a NVARCHAR variable?
英文:
I've created a stored procedure which should overwrite/update some column values in a database. The reason for that is that it's a backup from PROD to our TEST environment and I want to overwrite path/folder locations to another location as the PROD environment.
The database name can be different every time and I would like to set it only once in my stored procedure, just to prevent that I have to set it for every update statement.
So I did something like here below, but this is not accepted by SSMS:
DECLARE @DBNAME NVARCHAR(50)
SET @DBNAME = 'TEST'
UPDATE @DBNAME.[DBO].[SomeTable]
SET [code] = 'SomeCode'
UPDATE @DBNAME.[DBO].[AnotherTable]
SET [code] = 'AnotherCode'
What am I doing wrong here? Is it because I'm declaring it as a NVARCHAR variable?
答案1
得分: 1
你无法在T-SQL中参数化标识符。您需要将过程编写为动态SQL。假设过程的其余部分（包括数据）是静态的，那么这可以非常简单：
DECLARE @DBNAME sysname, -- 有关sysname，请参见下文
@SQL varchar(4000);
SET @DBNAME = 'TEST'
SET @SQL = 'UPDATE '+ @DBNAME +'.[DBO].[SomeTable]
SET [code] = ''SomeCode''' -- 请注意字符''的重复以转义它
EXEC(@SQL)
SET @SQL = 'UPDATE '+ @DBNAME +'.[DBO].[AnotherTable]
SET [code] = ''AnotherCode'''
EXEC(@SQL)
当您想要正确处理参数时，情况会稍微复杂一些，但并不是真的很复杂或难。
关于动态SQL的一个专业提示 - 在执行之前，您希望先打印它，然后将其粘贴到查询窗口中，这样更容易确保它执行的是它的设计用途。
从nchar和nvarchar（Transact-SQL）中获取信息：
sysname是一个系统提供的用户定义数据类型，与nvarchar(128)在功能上是等效的，只是它不可为空。sysname用于引用数据库对象名称。
英文:
You can't parameterize identifiers in T-SQL.
You'll have to write your procedure as dynamic SQL.
Assuming the rest of the procedure (including the data) is static, this can be very simple:
DECLARE @DBNAME sysname, -- See below about sysname
@SQL varchar(4000);
SET @DBNAME = 'TEST'
SET @SQL = 'UPDATE '+ @DBNAME +'.[DBO].[SomeTable]
SET [code] = ''SomeCode''' -- note the duplication of the ' char to escape it
EXEC(@SQL)
SET @SQL = 'UPDATE '+ @DBNAME +'.[DBO].[AnotherTable]
SET [code] = ''AnotherCode'''
EXEC(@SQL)
It gets a bit more cumbersome when you want to handle parameters correctly, but it's not really that complicated or hard.
One pro tip when it comes to dynamic SQL - You want to start by printing it before executing, and paste it into your query window, just so it will be easier to make sure it does what it's designed to do.
From nchar and nvarchar (Transact-SQL):
> sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it isn't nullable. sysname is used to reference database object names.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论