I use the database name in several places in my script, and I want to be able to quickly change it, so I'm looking for something like this:
DECLARE @DBNAME VARCHAR(50)
SET @DBNAME = 'TEST'
CREATE DATABASE @DBNAME
GO
ALTER DATABASE @DBNAME SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE @DBNAME SET RECOVERY SIMPLE
GO
But it doesn't work. So what's the correct way to write this code?
Put the entire script into a template string, with {SERVERNAME} placeholders. Then edit the string using:
SET @SQL_SCRIPT = REPLACE(@TEMPLATE, '{SERVERNAME}', @DBNAME)
and then run it with
EXECUTE (@SQL_SCRIPT)
It's hard to believe that, in the course of three years, nobody noticed that my code doesn't work!
You can't EXEC multiple batches. GO is a batch separator, not a T-SQL statement. It's necessary to build three separate strings, and then to EXEC each one after substitution.
I suppose one could do something "clever" by breaking the single template string into multiple rows by splitting on GO; I've done that in ADO.NET code.
And where did I get the word "SERVERNAME" from?
Here's some code that I just tested (and which works):
DECLARE @DBNAME VARCHAR(255)
SET @DBNAME = 'TestDB'
DECLARE @CREATE_TEMPLATE VARCHAR(MAX)
DECLARE @COMPAT_TEMPLATE VARCHAR(MAX)
DECLARE @RECOVERY_TEMPLATE VARCHAR(MAX)
SET @CREATE_TEMPLATE = 'CREATE DATABASE {DBNAME}'
SET @COMPAT_TEMPLATE='ALTER DATABASE {DBNAME} SET COMPATIBILITY_LEVEL = 90'
SET @RECOVERY_TEMPLATE='ALTER DATABASE {DBNAME} SET RECOVERY SIMPLE'
DECLARE @SQL_SCRIPT VARCHAR(MAX)
SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = REPLACE(@COMPAT_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = REPLACE(@RECOVERY_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)
SYSNAME would be a more appropriate datatype than VARCHAR(255) also should use QUOTENAME to deal with all possible database names (and possibly to prevent SQL injection depending on source of the name)
CREATE SCHEMA in other database, using USE {DBNAME}. Schema creates in wrong database ;/You can also use sqlcmd mode for this (enable this on the "Query" menu in Management Studio).
:setvar dbname "TEST"
CREATE DATABASE $(dbname)
GO
ALTER DATABASE $(dbname) SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE $(dbname) SET RECOVERY SIMPLE
GO
EDIT:
Check this MSDN article to set parameters via the SQLCMD tool.
Unfortunately you can't declare database names with a variable in that format.
For what you're trying to accomplish, you're going to need to wrap your statements within an EXEC() statement. So you'd have something like:
DECLARE @Sql varchar(max) ='CREATE DATABASE ' + @DBNAME
Then call
EXECUTE(@Sql) or sp_executesql(@Sql)
to execute the sql string.
EXEC looks for a stored procedure. In this case EXECUTE is needed.EXEC and EXECUTE are the same. I made the statement after failing with EXEC and succeeding with EXECUTE. Though obviously my real problem was unrelated to either.
You cannot use a variable in a create table statement. The best thing I can suggest is to write the entire query as a string and exec that.
Try something like this:
declare @query varchar(max);
set @query = 'create database TEST...';
exec (@query);
