CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jeff Foster
Jeff Foster

Reputation: 44746

Customizable database names and TempDB

I have a lump of SQL that looks a little like this

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{FOO}')
BEGIN
  EXECUTE ('CREATE DATABASE {FOO}')
  ALTER DATABASE {FOO} SET AUTO_CLOSE OFF
END

{FOO} is replaced at runtime with the name of a user configurable database. The logic is that I don't want to create the database if it already exists.

If {FOO} is tempdb then I get a failure when the query runs

Option 'AUTO_CLOSE' cannot be set in database 'tempdb'.

My question is why do I get this failure? SELECT * FROM sys.databases WHERE name = 'tempdb' returns zero results so surely my whole BEGIN/END pair shouldn't run? Indeed, if I put a print statement between begin and end, I don't see any output.

My guess is that SQL Server is doing some kind of linting on the SQL to make sure I don't muck around with tempdb? I have solved the problem by using EXECUTE instead, but I'm a little confused why I have to!

Upvotes: 0

Views: 72

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

Try ensuring both commands are separate and within dynamic SQL, then the change to tempdb won't be caught by the parser:

EXEC sp_executesql N'CREATE DATABASE {FOO};';
EXEC sp_executesql N'ALTER DATABASE {FOO} SET AUTO_CLOSE OFF;';

This is similar to the reason you can't do this:

IF 1 = 1
BEGIN
  CREATE TABLE #t1(id INT);
END
ELSE
BEGIN
  CREATE TABLE #t1(x NVARCHAR(255));
END

Even though you and I know that only one of those #t1 code paths will ever be reached, SQL Server presumes that both paths could be reached at runtime, and so complains at parse time.

Upvotes: 3

Related Questions