Variable declare error in SQL Server 2012

0
votes
use master

Declare @CID integer = 1114
Declare @dbname varchar(50)

set  @dbname = (select DatabaseName from [Clients].[dbo].[Client] where clientid = @CID)
select @dbname

alter database @dbname 
  set single_user with rollback immediate
drop database @dbname

delete from [Clients].[dbo].[client] where clientid = @CID

But I get errors

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@dbname'.

Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'immediate'.

2
sqlsql-serversql-server-2012
You cannot pass dbname as variable, you should use Dynamic-SQL – Lukasz Szozda
possible duplicate of Incorrect syntax near @[parameter name] when using parameterised Alter Database command – DavidG

2 Answers

2
votes

your problem isn't with the declaration, it's with the usage here

alter database @dbname set single_user with rollback immediate

There are places where you can use variables in sql statements, typically in the where clause, but there are some limitations on where you can use them.

You could go with something like this

use master
Declare @CID integer = 1114
Declare @dbname nvarchar(50)
set  @dbname = (select DatabaseName from [Clients].[dbo].[Client] where clientid=@CID)
select @dbname
declare @sql nvarchar (2000) = N'alter database '+ @dbname + N' set single_user with rollback immediate'
exec sp_executeSql @sql
select @sql = N'drop database ' + @dbname
exec sp_executeSql @sql

delete from [Clients].[dbo].[client] where clientid=@CID
1
votes

Please try following script first on a development or test instance before you use it on a productive one

use master
Declare @CID integer = 1114
Declare @dbname varchar(50)
set  @dbname = (select DatabaseName from [Clients].[dbo].[Client] where clientid=@CID)
--select @dbname

declare @sql nvarchar(max)
set @sql = 'use ' + @dbname + '
alter database ' + @dbname + ' set single_user with rollback immediate
use master
drop database ' + @dbname
print @sql
-- exec sp_executesql @sql

delete from [Clients].[dbo].[client] where clientid=@CID

Please note that before I drop the database, I switch to master database using "USE master"

To drop database uncomment "exec sp_executesql @sql" statement I used a PRINT command so that you can see what you will execute before hand.

You can find info on how to run dynamic sql using sp_executesql at given tutorial with sample