Posted by Hugo Kornelis on 10/05/81 11:37

On 17 Jan 2006 08:14:12 -0800, Cecil wrote:

>I get this error:
>Msg 137, Level 15, State 2, Line 2
>Must declare the scalar variable "@DBName".
>
>
>W/ this code:
>
>--Constants
> DECLARE @DBName varchar(20);
> SET @DBName = 'Base';
> GO
>
>
>--Create & Use Database @DBName
> USE master
> GO
>
> if exists (select * from sysdatabases where name=@DBName)
> drop database @DBName
> GO
>
> CREATE DATABASE @DBName
> GO
> use @DBName
> GO
>
>How should this be done?

Hi Cecil,

As Jack points out: GO is a batch terminator, and the variable goes out
of scope when the batch is terminated.

However, removing the GO's won't fix this. Neither DROP or CREATE
DATABASE, nor USE will take a variable as database name.

It's highly uncommon to create a database with a name that is only known
at run-time. Are yoou sure that this is the correct answer to your
problem? What *IS* the actual business problem that you're trying to
solve?


Anywway, if you MUST do it like this, you'll have to use dynamic SQL.
Someting like this:

DECLARE @SQL varchar(8000)
SET @SQL = 'USE master; DROP DATABASE ' + @DBname + '; CREATE DATABASE '
+ @DBName + ';'
EXEC (@SQL)

DISCLAIMER: Dynamic SQL has some dangerous hazards. SQL injection is the
most important. Make sure to read and understand Erland's article before
proceeding: http://www.sommarskog.se/dynamic_sql.html.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]