如何在 T-SQL 中使用变量作为数据库名称? [英] How to use a variable for the database name in T-SQL?

问题描述

我在脚本中的几个地方使用了数据库名称，并且我希望能够快速更改它，所以我正在寻找这样的东西:

I use the database name in several places in my script, and I want to be able to quickly change it, so I'm looking for something like this:

DECLARE @DBNAME VARCHAR(50)
SET @DBNAME = 'TEST'

CREATE DATABASE @DBNAME
GO
ALTER DATABASE @DBNAME SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE @DBNAME SET RECOVERY SIMPLE 
GO

但它不起作用.那么这段代码的正确写法是什么?

But it doesn't work. So what's the correct way to write this code?

推荐答案

将整个脚本放入模板字符串中，使用 {SERVERNAME} 占位符.然后使用以下命令编辑字符串:

Put the entire script into a template string, with {SERVERNAME} placeholders. Then edit the string using:

SET @SQL_SCRIPT = REPLACE(@TEMPLATE, '{SERVERNAME}', @DBNAME)

然后用

EXECUTE (@SQL_SCRIPT)

<小时>

很难相信，在三年的时间里，没有人注意到我的代码不起作用！

您不能EXEC 多个批次.GO 是批处理分隔符，而不是 T-SQL 语句.需要构建三个单独的字符串，然后在替换后分别EXEC.

You can't EXEC multiple batches. GO is a batch separator, not a T-SQL statement. It's necessary to build three separate strings, and then to EXEC each one after substitution.

我想人们可以通过在 GO 上拆分将单个模板字符串分成多行来做一些聪明"的事情；我已经在 ADO.NET 代码中做到了.

I suppose one could do something "clever" by breaking the single template string into multiple rows by splitting on GO; I've done that in ADO.NET code.

我是从哪里得到SERVERNAME"这个词的?

And where did I get the word "SERVERNAME" from?

这是我刚刚测试过的一些代码(并且有效):

Here's some code that I just tested (and which works):

DECLARE @DBNAME VARCHAR(255)
SET @DBNAME = 'TestDB'

DECLARE @CREATE_TEMPLATE VARCHAR(MAX)
DECLARE @COMPAT_TEMPLATE VARCHAR(MAX)
DECLARE @RECOVERY_TEMPLATE VARCHAR(MAX)

SET @CREATE_TEMPLATE = 'CREATE DATABASE {DBNAME}'
SET @COMPAT_TEMPLATE='ALTER DATABASE {DBNAME} SET COMPATIBILITY_LEVEL = 90'
SET @RECOVERY_TEMPLATE='ALTER DATABASE {DBNAME} SET RECOVERY SIMPLE'

DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@COMPAT_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@RECOVERY_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

这篇关于如何在 T-SQL 中使用变量作为数据库名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！