DROP DATABASE with variable not possible?

  • FreeHansje

    SSChampion

    Points: 11751

    Hi,

    I wish to drop a database when it exists, like this:

    DECLARE @DBNaam varchar(50), @file1 varchar(200), @file2 varchar(200)

    SET @DBNaam = 'DBName2'

    IF EXISTS(SELECT DBID FROM sysdatabases

    WHERE name = @DBNaam)

    BEGIN

    DROP DATABASE @DBNaam;

    END

    I receive an error, indicating a variable cannot be used in the DROP DATABASE command. Now I can easily make a dynamic sql-statement and execute that, which I would rather not do.

    Can any1 explain why this does not work? Do I miss something here?

    Greetz,
    Hans Brouwer

  • arun.sas

    SSChampion

    Points: 11831

    FreeHansje (7/6/2009)


    DECLARE @DBNaam varchar(50), @file1 varchar(200), @file2 varchar(200)

    SET @DBNaam = 'DBName2'

    IF EXISTS(SELECT DBID FROM sysdatabases

    WHERE name = @DBNaam)

    BEGIN

    DROP DATABASE @DBNaam;

    END

    Hi,

    try this

    DECLARE @DBNaam varchar(50),

    @DROP nvarchar(100),

    @RESULT nvarchar(200)

    set @DBNaam = 'DBName2'

    set @DROP = 'DROP DATABASE'

    set @RESULT = (@DROP+' '+@DBNaam)

    exec sp_executesql @RESULT

    ARUN SAS

  • Gail Shaw

    SSC Guru

    Points: 1004474

    FreeHansje (7/6/2009)


    I receive an error, indicating a variable cannot be used in the DROP DATABASE command. Now I can easily make a dynamic sql-statement and execute that, which I would rather not do.

    Can any1 explain why this does not work? Do I miss something here?

    None of the DDL statements (create/drop database, create/drop table, create/drop view...) allow variables for the name of the object. If you want to do this, you will have to use dynamic SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • FreeHansje

    SSChampion

    Points: 11751

    Tnx for answering; I'll use dynamic sql.

    Greetz,
    Hans Brouwer

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply