Incorrect syntax near @[parameter name] when using parameterised Alter Database command

Question

I'm trying to drop a SQL Server database using the following code:

SqlCommand command = new SqlCommand("USE MASTER; ALTER DATABASE @database SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE @Database", connection);
command.Parameters.AddWithValue("@database", TestingEnvironment.DatabaseName);
command.ExecuteNonQuery();

When I execute it, I get the error:

Incorrect syntax near '@database'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near 'IMMEDIATE'.

What am I doing wrong?

Mladen Prajdic · Accepted Answer · 2008-11-10 17:28:51Z

5

putting it simply the Alter Database command doesn't support parameters as you want it to. you'll have to concat strings here.

answered Nov 10, 2008 at 17:28

Mladen Prajdic

15.7k2 gold badges46 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samuel Jack Over a year ago

Mladen, That was the conclusion I came to. Thanks for confirming it.

Mr. Flibble · Accepted Answer · 2008-11-10 17:05:15Z

0

Are the Params case sensitive? You have a capital D in the 2nd @Database.

answered Nov 10, 2008 at 17:05

Mr. Flibble

3412 gold badges9 silver badges21 bronze badges

2 Comments

Samuel Jack Over a year ago

Good thought, but actually that doesn't seem to make a difference

Mr. Flibble Over a year ago

And can you add a param twice like that, with one add param? Might want to try using GO instead of ';'...

Collectives™ on Stack Overflow

Incorrect syntax near @[parameter name] when using parameterised Alter Database command

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related