[SQL Server]CREATE/ALTER DATABASE不能使用Transaction

果然是失敗、錯誤中學習成長，即便是有Common Sense就可以了解的東西，我仍然可以試出不可行的方法，可見我武藝精深、非同小可。

近來需要實作動態建立SQL Server資料庫，小弟我只能想到利用動態T-SQL來實作，一切都進行得很順利，但是突然靈光一閃~阿要是建失敗了怎麼辦？

這怎麼可能難的倒我呢！包上Transaction不就搞定啦！

於是我蹲好馬步，順著這股氣勢寫了一段SQL...

declare @DBName nvarchar(50), @Sqlcmd nvarchar(max)
set @DBName = N'HowardDB'

Begin Tran CreateDB
set @Sqlcmd = 
 'USE [master]

 CREATE DATABASE [' + @DBName + '] ON  PRIMARY 
 ( NAME = N''' + @DBName + ''', 
 FILENAME = N''C:\Program Files\Microsoft SQL Server
 \MSSQL10.SQLEXPRESS\MSSQL\DATA\' + @DBName + '.mdf'',
 SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  LOG ON 
 ( NAME = N''' + @DBName + '_log'', 
 FILENAME = N''C:\Program Files\Microsoft SQL Server
 \MSSQL10.SQLEXPRESS\MSSQL\DATA\' + @DBName + '_log.ldf'', 
 SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 
 ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 100'

BEGIN TRY
 execute sp_executesql @Sqlcmd
 Commit Tran CreateDB
END TRY
BEGIN CATCH
 select ERROR_MESSAGE() as ErrorMessage
 Rollback Tran CreateDB
END CATCH

招式都已經擬定完畢，接著看準時機按下F5...

花惹發~竟然吐出錯誤訊息：

多重陳述式的交易內不允許 CREATE DATABASE 陳述式。

原來因Create/Alter Database建立修改失敗即等同Transaction(阿就沒建成功、沒改成功是要回復甚麼)。

最後只好使用Drop Database來製作類似Transaction機制，在建立、編輯資料庫過程中發生錯誤，直接刪除資料庫來還原至乾淨的狀態。

declare @DBName nvarchar(50), @Sqlcmd nvarchar(max)
set @DBName = N'HowardDB'
Set @Sqlcmd = 
 'USE [master]
 ALTER DATABASE [' + @DBName + '] 
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 drop database [' + @DBName + ']'
execute sp_executesql @Sqlcmd

p.s.其實在撰寫範例過程中，還有發現一些關於預存程序、動態T-SQL、Error Handling相關的有趣現象，找機會再來記一下好了！