DROP DATABASE with variable not possible?

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  July 6, 2009 at 3:48 am

  #133660

  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

  More actions

  July 6, 2009 at 4:38 am

  #1020272

  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: 1004504

  More actions

  July 6, 2009 at 4:54 am

  #1020280

  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

  More actions

  July 6, 2009 at 5:02 am

  #1020283

  Tnx for answering; I'll use dynamic sql.

  Greetz,
  Hans Brouwer

• wally_pons

  SSC Rookie

  Points: 30

  More actions

  October 30, 2022 at 5:29 pm

  #4106533

  /*

  This is my approach to this case

  */

   

  DECLARE @DBname AS NVARCHAR(100)

  DECLARE @Drop AS NVARCHAR(100)

  DECLARE @Result AS NVARCHAR(100)

  SET @DBname = 'Your_DB_Name' -- Replcae 'Your_DB_Name' with the database name of your choice

  SET @DROP = 'DROP DATABASE'

  SET @Result = (@Drop+ ' '+@DBname)

  IF DB_ID(@DBname) IS NOT NULL

  BEGIN

  Print ''

  Print 'Dear ' +SUSER_NAME()+', Successfully dropping the database: ' + @DBname

  Exec sp_executesql @Result

  END

  Else

  BEGIN

  Print ''

  Print 'Dear ' +SUSER_NAME()+ ', Nothing to drop, database '+@DBname+' does not exists...'

  END