Use variable for FILENAME

Instead of

CREATE DATABASE [MyDB] ON (NAME = N'MyDB_Data', FILENAME = N'C:MyDB.mdf' )
LOG ON (NAME = N'MyDB_Log', FILENAME = N'C:MyDB.LDF' )

I want to write something like:

DECLARE @Path VarChar(100)
SET @Path = 'C:'
CREATE DATABASE [MyDB] ON (NAME = N'MyDB_Data', FILENAME = @Path+'MyDB.mdf' )
LOG ON (NAME = N'MyDB_Log', FILENAME = @Path+'MyDB.LDF' )

But I get error when compile in QA. How can I use variables for FILENAME in CREATE DATABASE?

CanadaDBA

You need to use dynamic SQL. Try something like this:

declare @sqlstring varchar(1024), @path varchar(100)
set @path = 'C:'
set @sqlstring = 'CREATE DATABASE MyDB ON (NAME = ''MyDB_data'', FILENAME = '''+@PATH+'MyDB.mdf''
LOG ON (NAME = N''MyDB_Log'', FILENAME = '''+@Path+'MyDB.LDF'')'

exec (@sqlstring)

Thanks ykchakri and Frank. I think it will solve my problem. Any how, is this the only way?

CanadaDBA

Hey Frank,
It looks good site. There is a good article about dynamic sql. Thanks

quote:Originally posted by FrankKalis

Yes, I don't know of any other way.
For everything that is related to dynamic sql, I like to refer people tohttp://www.sommarskog.se. Erland's article on dynamic sql must be one of the most referenced one in the SQL Server community, I believe.

--Frank
http://www.insidesql.de

CanadaDBA