Monday, June 14, 2010

Condition Database Drop on SQL Azure

by Wayne Walter Berry via SQL Azure Team Blog on 6/14/2010 5:39:52 PM

SQL Azure’s Transact-SQL syntax requires the DROP DATABASE statement be the only statement executed in the batch. For more information, see the MSDN documentation. So how do you execute a conditional drop? Maybe you only want to drop the database when the database exists. Here is how:

DECLARE @DBName SYSNAME, @Command NVARCHAR(MAX)
SELECT @DBName = 'db1'
IF EXISTS (SELECT * FROM sys.databases WHERE name=@DBName)
BEGIN
    SELECT @Command = 'DROP DATABASE [' + @DBName + '];'
    EXEC @Command
END

One batch contains the conditional statement and it generates and executes (in another batch) the DROP DATABASE statement.

Do you have questions, concerns, comments? Post them below and we will try to address them.