How do I drop a declared variable DB if it exists?

Question

I'm trying to drop a declare variable DB name if it already exists. I have the following, but keep on getting a syntax error. Incorrect syntax near '@DB_NAME'.

USE MASTER  
GO


DECLARE @DB_NAME AS VARCHAR(50) 
SET @DB_NAME = 'TEST' 
DECLARE @DB_PATH AS VARCHAR(200) 
SET @DB_PATH = N'C:\\Clients\\TEST PILOT\\F - Raw Data\\'


IF EXISTS(SELECT * FROM sys.sysdatabases where name=@DB_NAME) 
DROP @DB_NAME

CREATE DATABASE @DB_NAME  
ON 
       (NAME = N'@DB_NAME', 
    FILENAME = N'C:\\Clients\\TEST PILOT\\F - Raw Data\\TEST.mdf', 
    SIZE = 1GB , 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1GB
    )

LOG ON
    (NAME = N'TEST_log', 
     FILENAME = N'C:\\Clients\\TEST PILOT\\F - Raw Data\\TEST.ldf', 
     SIZE = 2024KB , 
     MAXSIZE = 2GB , 
     FILEGROWTH = 10%
    )
GO

http://stackoverflow.com/questions/727788/how-to-use-a-variable-for-the-database-name-in-t-sql — David Brabant, Feb 04 '16 at 11:58
I think the problem was the incorrect use of backslash. Which @DavidBrabant fixed in the edit. The code in the question should run without problems — Haytem BrB, Feb 04 '16 at 12:00
Drop statement is not correct change it to DROP DATABASE [@DB_NAME] and similarly change the create also like CREATE DATABASE [@DB_NAME] — PSK, Feb 04 '16 at 12:03

score 0 · Accepted Answer · answered Feb 04 '16 at 12:01

You cannot create a variable type for Database;

If i'm not wrong you want to delete a database by input in the variable

You can use some dynamic sql to do the same This is not exact script you can adjust the dynamic sql and get your desired result

USE MASTER
GO

DECLARE @DB_NAME AS VARCHAR(50),@ex_sql varchar(max)
SET @DB_NAME = 'TEST'
DECLARE @DB_PATH AS VARCHAR(200)
SET @DB_PATH = 'C:\Clients\TEST PILOT\F - Raw Data\'

IF EXISTS(SELECT * FROM sys.sysdatabases where name=@DB_NAME)
begin
set @ex_sql='drop database '+@DB_NAME
exec @ex_sql
end 
set @ex_sql=''
set @ex_sql='CREATE DATABASE '+@DB_NAME+
' ON 
(NAME = '''+@DB_NAME+''', 
    FILENAME = ''C:\Clients\TEST PILOT\F - Raw Data\TEST.mdf'', 
    SIZE = 1GB , 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1GB
    )

LOG ON
    (NAME = ''TEST_log'', 
     FILENAME = ''C:\Clients\TEST PILOT\F - Raw Data\TEST.ldf'', 
     SIZE = 2024KB , 
     MAXSIZE = 2GB , 
     FILEGROWTH = 10%
    )
GO'
exec @ex_sql

How do I drop a declared variable DB if it exists?

1 Answers1