title: How to create SQL database using EXEC() statement
body: <p>Need to create a folders and databases inside them. Since there is no ability to create databases using variable name and path I'm trying to use  EXEC() statement. But it fails with error 'Incorrect syntax near 'LOG''. What I'm doing wrong?</p>
  
  <pre><code>USE Master;
  GO
  SET NOCOUNT ON
  
  -- 1 - Variable declaration
  DECLARE @DBName1 sysname
  DECLARE @DataPath1 nvarchar(500),
  DECLARE @LogPath1 nvarchar(500)
  DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
  DECLARE @version sysname
  declare @sql nvarchar(500)
  declare @fulldbpath varchar(500)
  declare @fulllogpath varchar(500)
  
  -- 2 - Initialize variables
  SET @version= 
        CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 4)
          WHEN '9.00' THEN '2005'
          WHEN '10.0' THEN '2008'
          WHEN '10.5' THEN '2008R2'
          WHEN '11.0' THEN '2012'
          WHEN '12.0' THEN '2014'
      END
  SET @DBName1 = 'DB1'
  SET @DataPath1 = 'G:\StorageGroup1\SQL' + @version+ '\'+ @DBName1 
  SET @LogPath1 = 'F:\StorageGroup1\SQL\' + @version+ '\'+ @DBName1
  SET @fulldbpath=@DataPath1+'\'+@DBName1+'.mdf'
  SET @fulllogpath=@LogPath1+'\'+@DBName1+'_log.ldf'
  
  -- 3 - @DataPath values
  INSERT INTO @DirTree(subdirectory, depth)
  EXEC master.sys.xp_dirtree @DataPath1
  
  -- 4 - Create the @DataPath directory
  IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName1)
  EXEC master.dbo.xp_create_subdir @DataPath1
  
  -- 5 - Remove all records from @DirTree
  DELETE FROM @DirTree
  
  -- 6 - @LogPath values
  INSERT INTO @DirTree(subdirectory, depth)
  EXEC master.sys.xp_dirtree @LogPath1
  
  -- 7 - Create the @LogPath directory
  IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName1)
  EXEC master.dbo.xp_create_subdir @LogPath1
  
  
  --8 -Create database
    set @sql = 'CREATE DATABASE [@DBName1]
      ON (NAME = [@DBName1],
          FILENAME = [@fulldbpath]
      LOG ON (NAME = [@DBName1]
          FILENAME = [@fulllogpath]
              )'                      
      EXEC(@sql)          
  
  
  SET NOCOUNT OFF
  GO
  </code></pre>