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)
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 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