Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with: Drop Database @dbname

Status
Not open for further replies.

jluost1

Programmer
Joined
Jun 8, 2001
Messages
78
Location
US
I want to drop a database programmingly. Following is my code:
'---------------------------------------
CREATE PROCEDURE PROC_DELETE_DB
@dbname varchar(50)
AS

IF EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME=@dbname)
BEGIN
DROP DATABASE @dbname
END
'-------------------------------------

Unfortunately, It doesn't work and the error is in "DROP DATABASE" statment.

I tried the following in QA:
'----------------------
DROP DATABASE 'test_db'
'----------------------

It generate the same error. but this statement is ok:
'-----------------------
DROP DATABASE test_db
'-----------------------

So, how can I make my procedure work with

'---------------------
DROP DATABASE @dbname
'---------------------




 

Create a dynamic SQL statement and execute it.

CREATE PROCEDURE PROC_DELETE_DB
@dbname varchar(50)
AS

IF EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME=@dbname)
BEGIN
DECLARE @SQL nvarchar(200)
SET @SQL='DROP DATABASE ' + @dbname
EXECUTE(@SQL)
END Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top