SQL Server Performance Forum – Threads Archive

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 = ”’[email protected]+’MyDB.mdf”
LOG ON (NAME = N”MyDB_Log”, FILENAME = ”’[email protected]+’MyDB.LDF”)’ exec (@sqlstring)

---

Just a small addition. If it needs to be dynamic sql, you should use sp_executesql rather than exec. BOL has the explanation why –Frank
http://www.insidesql.de

---

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

---

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

---

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

CanadaDBA

---

]]>