为什么我不能用变量代替字符串作为参数呢

Why does this work:

为什么这个工作:

ALTER DATABASE TEST2 MODIFY FILE ( NAME = TEST1, NEWNAME = TEST2, FILENAME = 'C:\Data\TEST2')

But this not work:

但这不是工作:

DECLARE @PATH NVARCHAR(255)
SET     @PATH = 'C:\Data\TEST2'
ALTER DATABASE TEST2 MODIFY FILE ( NAME = TEST1, NEWNAME = TEST2, FILENAME = @PATH)

I get the error:

我得到了错误:

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '@PATH'.

Msg 102，第15级，状态1，第5行错误语法，在“@PATH”附近。

2 个解决方案

#1

I believe this is because of ALTER specifics, and when you are specifying a value explicitly it would be a constant, in case of variable - value will be resolved in run time.

我认为这是由于修改的细节，当您显式地指定一个值时，它将是一个常量，如果变量-值将在运行时得到解析。

Anyway you can pass a custom variable value using Dynamic Sql:

无论如何，您可以使用动态Sql传递自定义变量值:

DECLARE @name varchar(128)
DECLARE @query varchar(255)
SET @name = '...'
SET @query = 'ALTER DATABASE TEST2 MODIFY FILE ( NAME = TEST1, NEWNAME = TEST2, FILENAME =  ' 
             + @name 
             + ')'
EXEC(@query)

#2

from "ALTER DATABASE File and Filegroup Options" on MSDN:

来自MSDN上的“变更数据库文件和文件组选项”:

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = {'os_file_name' | 'filestream_path' } ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
)

You'll note that FILENAME is explicitly listed as being something that starts and ends with a ' quote mark. So, only literal strings are supported here.

您将注意到文件名被显式地列出为以“引号”开头和结尾的内容。这里只支持文字字符串。

That's the unsatisfactory answer - it doesn't give the underlying reason - but since I don't work for MS, I can't give a deeper answer. I'd suspect it's related to a lot of cruft in the T-SQL syntax, and the parser still being a remarkably simple/stupid one that they don't want to modify too much (except for adding new features).

这是一个不令人满意的答案——它没有给出根本的原因——但由于我没有为MS工作，我无法给出一个更深层次的答案。我怀疑它与T-SQL语法中的许多cruft有关，解析器仍然是一个非常简单/愚蠢的解析器，他们不希望修改太多(除了添加新特性)。

#1