Variables declaration in Create Database Script

Question

I have a large database creation query the beginning of which is as follows:

USE Master
GO

IF EXISTS(SELECT * FROM sys.sysdatabases where name = 'MyDatabase')
    DROP DATABASE MyDatabase
GO
CREATE DATABASE MyDatabase
GO
USE MyDatabase
GO

I want to declare a variable at the beginning like this:

DECLARE @MainDB VARCHAR(30) = NULL
USE Master
GO

IF EXISTS(SELECT * FROM sys.sysdatabases where name = @MainDB)
    DROP DATABASE @MainDB
GO
CREATE DATABASE @MainDB
GO
USE @MainDB
GO

I would execute this query from the command line with the new database name being assigned using the sqlcmd tool. however sql is telling me that the variable @MainDB is not declared. Is this something I can do? If not how would you recommend I work around this problem?

See [this other SO question (and its answers) on the same topic](http://stackoverflow.com/questions/5321287/setting-database-name-as-a-variable-in-sql) — marc_s, Oct 09 '12 at 19:48
Also, variable scope is limited to a single query batch (everything between two GO statements). — Andrew Lewis, Oct 09 '12 at 19:52
possible duplicate of [How to use variable for database name in t-sql](http://stackoverflow.com/questions/727788/how-to-use-variable-for-database-name-in-t-sql) — Pondlife, Oct 09 '12 at 21:24

score 0 · Answer 1 · edited May 23 '17 at 12:18

0

This question is kind of a rehash.

How to use a variable for the database name in T-SQL?

The short answer is you can't have a variable database name. You have to put your T-SQL into a string/VARCHAR(MAX), do a find and replace, then execute it.

edited May 23 '17 at 12:18

Community

1
1

answered Oct 09 '12 at 19:47

Louis Ricci

20,804
5
48
62

score 0 · Answer 2 · answered Oct 09 '12 at 19:53

Sorry, but you cannot do it like this, because SQL variables only have "batch"-scope and those "GO" commands indicate the boundaries of your batches. Thus, every time you pass GO, all of your variables get wiped out (they're not even Declared any more).

There are several ways around this:

Get Rid of the GO's: This is what we do when we need a Stored Procedure (which cannot have any GOs in it) instead of a script, but it is a fairly complicated series of sophisticated tricks that have to be wrapped around each other in order to pull it off. You have to be pretty T-SQL adept to use it.
Use a #temp table to hold your values instead. This works because #temp table have session-scope instead or just batch-scope.
I believe that you can also use and manipulate NTDOS environment variables from SQLCMD as well, though I am not familiar with exactly how to do it.

If you want to pursue any of these, let me know which one and I can explain in more detail with examples.

Oops, I missed the "variable Database name" part of this. That can be done as well, but you have to add dynamic SQL to the mix.

FYI: the link from LastCoder's answer is showing how to do #1, above, which also works well with the dynamic SQL requirement. — RBarryYoung, Oct 09 '12 at 19:57

score 0 · Answer 3 · answered Oct 09 '12 at 19:54

You have multiple issues... GO ends the current batch. So your variable is not visible anymore. You need to move declaration. Also, you cannot DROP DATABASE @MainDB, you have to use dynamic queries. For example,

GO 
DECLARE @MainDB VARCHAR(30) = 'MyDB';
IF EXISTS(SELECT * FROM sys.sysdatabases where name = @MainDB)
BEGIN
  EXECUTE ('DROP DATABASE '+@MainDB);
END;

score 0 · Answer 4 · answered Oct 09 '12 at 22:00

You say you need to run this from the command line, so if you have a lot of code which depends on the structure you have already built you can do the following:

Create a temporary copy of the script
Search and replace @MainDB with the DB name you pass in as the parameter in the temporary script
Run the temporary script using the sqlcmd tool

Obviously, remove the DECLARE @MainDB varchar(30) = NULL from your script if you like this option.

If you choose this approach, you can implement your 3 steps using a variety of different technologies (powershell, python, batch file, VBScript ...).

VBScript file approach:

set obj = CreateObject("Scriptlet.TypeLib")  
tempsqlfile = obj.GUID & ".sql" 'get a new name for your sql file

set fso = CreateObject("Scripting.FileSystemObject")
set objFile = objFSO.OpenTextFile(tempsqlfile, ForReading) 'open the template file
strSQLText = objFile.ReadAll
objFile.Close

strNewSQLText = Replace(strSQLText, "@MainDB", Wscript.Arguments(1)) 'replace the db name
Set objFile = objFSO.OpenTextFile(tempsqlfile, ForWriting)
objFile.WriteLine strNewText 'write the new file
objFile.Close

Set Shell = WScript.CreateObject("WScript.Shell")
commandLine = "osql -E -i " & Wscript.Arguments(0) & -o " & tempsqlfile & ".rpt"
Set oExec = Shell.Exec(commandLine)

Apologies for the variable names - I cut and pasted bits and pieces from various places but you should get the gist.

(Also - apologies for choosing VBScript out of all those options and be aware that there is no error checking for missing parameters)

As it stands above, if you save that script as 'runmystuff.vbs' then you can do:

runmystuff.vbs sqlfile.sql MagicNewDB

This will replace @MainDB with MagicNewDB everywhere inside the script and then run it using osql.

score 0 · Accepted Answer · answered Oct 10 '12 at 18:34

Found a way to make cmdline variables equal t-sql variables

USE master
GO
DECLARE @Mydb VARCHAR(30) = "$(mydb)"

IF EXISTS(SELECT * FROM sys.sysdatabases where name = @Mydb)
    print @Mydb
    Execute('create database ' + @Mydb)

The batch file I run from looks like this.

sqlcmd -S %1 -i CreateDatabases.sql -v mydb="%2"

I can now run from sqlcmd and enter my server for %1 and desired DB name for %2.

thanks everyone for the replies they all helped me find the right solution.

Variables declaration in Create Database Script

5 Answers5