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?
This question is related to
sql-server
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)
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.
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.
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);
Source: Stackoverflow.com