SQL Server Performance

Use variable for FILENAME

Discussion in 'General DBA Questions' started by CanadaDBA, Jul 30, 2004.

  1. CanadaDBA New Member

    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
  2. ykchakri New Member

    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)
  3. FrankKalis Moderator

    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
  4. CanadaDBA New Member

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

    CanadaDBA
  5. FrankKalis Moderator

    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
  6. CanadaDBA New Member

    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

Share This Page