ALTER DATABASE / ADD FILE / VARIABLE FILENAME?

问题:

I want to add a file/file group to an existing database, but I need to get the path from a variable because it will be different when this script is finalized. When I check the script in SQL Management Studio 2008 R2, it returns an error at FILENAME = @Path.

How can I use that variable?

SCRIPT WILL NOT BE RUN FROM THE COMMAND LINE!

ALTER DATABASE [MyDB]
ADD FILEGROUP [MyDB_FileStream] CONTAINS FILESTREAM
GO

DECLARE @Path VARCHAR(MAX)
SET @Path = 'C:\whatEverIWantItToBe\ThisCouldChangeWithLogic\YouGetThePoint\'

ALTER DATABASE [MyDB]
ADD FILE
  (NAME = 'MyDB_FileStream'
   , FILENAME = @Path
   )
TO FILEGROUP [MyDB_FileStream]

回答1:

Using dynamic SQL:

Declare @Path nvarchar(max)
Declare @Sql nvarchar(max)

Set @Path = 'C:\whatEverIWantItToBe\ThisCouldChangeWithLogic\YouGetThePoint\'

Set @Sql = 'Alter Database [MyDb]
    Add File( Name = ''MyDb_FileStream''
            , FileName = ' + QuoteName( @Path, '''' ) 
            + ') To FileGroup [MyDbFileStream]'

Exec( @Sql )

回答2:

Not sure if that will work for you but if you're going to run the script from a command line you could do:

ALTER DATABASE [MyDB]
ADD FILE (NAME = 'MyDB_FileStream', FILENAME = $(path))
TO FILEGROUP [MyDB_FileStream]

And run it with:

sqlcmd -s servername -i script.sql -v path="path to the file"

回答3:

I haven't tried, but usually dynamic sql can help to solve this kind problem