July 6, 2009 at 3:48 am
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
July 6, 2009 at 4:38 am
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
July 6, 2009 at 4:54 am
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
July 6, 2009 at 5:02 am
Tnx for answering; I'll use dynamic sql.
Greetz,
Hans Brouwer
October 30, 2022 at 5:29 pm
/*
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply