Why can't I use variables in T-SQL like I imagine I can?

Question

Forgive me, I am a developer who has moved over to the world of SQL. I thought I could improve some SQL by adding variables but it did not function like I expected. Can someone tell me why this does not work? I don't want a work around, I want to know the reasons why this doesn't work like I imagine it should as I am sure there is a good reason, but currently it doesn't jump out at me.

DECLARE @DatabaseName varchar(150)
SET @DatabaseName = 'MyAmazingDatabaseName'

CREATE DATABASE @DatabaseName
GO

USE @DatabaseName
GO

Answer 1

Per the Books online page for variables

Variables can be used only in expressions, not in place of object names or keywords. To construct dynamic SQL statements, use EXECUTE.

It would work the way you were expecting if, for example, you used your variable in a where clause. As for why, I would think it has something to do with the parser not able to evaluate the variable and thus check for existence. When executing, the query is parsed first for syntax and objects and then, if parsing successful, the query executes at which point the variable would be set.

DECLARE @name varchar(20);
SET @name = 'test';

CREATE TABLE [#tmp]([val] varchar(10));

insert into #tmp
values('test')

SELECT *
FROM [#tmp]
WHERE [val] = @name;

Answer 2

The limitations on the use of variables in SQL statements arise from the architecture of SQL.

There are three phases in the processing of an SQL statement:

1. Preparation - The statement is parsed and an execution plan is compiled, specifying which database objects are accessed, how they are accessed and how they are related. The execution plan is saved in the plan cache.
2. Binding - any variables in the statement are replaced with actual values.
3. Execution - the cached plan is executed with the bound values.

SQL server hides the preparation step from the programmer and executes it much faster than more traditional databases such as Oracle and DB2. It's for performance reasons that SQL spends potentially a lot of time determining an optimal execution plan, but only does it the first time the statement is encountered after a restart.

So in static SQL, variables may only be used in places where they will not invalidate the execution plan, so not for table names, column names (including column names in WHERE conditions), etc.

Dynamic SQL exists for the cases where one cannot work round the restrictions, and the programmer knows that it will take slightly longer to execute. Dynamic SQL can be vulnerable to malicious code injection, so take care!

Answer 3

As you can see, the "why" question requires a different kind of answer, including historical rationale and underlying assumptions for the language, I'm not sure I can really do that justice.

This comprehensive article by SQL MVP Erland Sommarskog does attempt to provide some rationale, along with the mechanics:

The Curse and Blessings of Dynamic SQL:

Caching Query Plans

Every query you run in SQL Server requires a query plan. When you run a query the first time, SQL Server builds a query plan for it – or as the terminology goes – it compiles the query. SQL Server saves the plan in cache, and next time you run the query, the plan is reused.

This (and security, see below) is probably the biggest reason.

SQL operates under the premise that queries are not one-time operations, but that they will be used over and over. If the table (or the database!) is not actually specified in the query, it has no way to generate and save an execution plan for future use.

Yes, not every query we run will be re-used, but this is the default operating premise of SQL, so "exceptions" are meant to be exceptional.

A few other reasons Erland lists (note that he's explicitly listing advantages of using stored procedures, but many of these are also advantages of parameterized (non-dynamic) queries):

• The Permission System: the SQL engine can't predict whether you have the rights to run a query if it doesn't know the table (or database) you will be operating against. "Permission chains" using dynamic SQL is a pain in the butt.
• Reducing Network Traffic: Passing the name of the stored proc and a few parameter values over the network is shorter than a long query statement.
• Encapsulating Logic: You should be familiar with the advantages of encapsulating logic from other programming environments.
• Keeping Track of what Is Used: If I need to change a column definition, how can I find all the code that calls it? System procedures exists to find dependencies within a SQL database, but only if the code is in stored procedures.
• Ease of Writing SQL Code: Syntax check occurs when you create or modify a stored procedure, so hopefully fewer errors result.
• Addressing Bugs and Problems: A DBA can trace and measure the performance of individual stored procedures much more easily than ever-changing dynamic SQL.

Again, each of these has a hundred nuances that I won't get into here.

Answer 4

You need to use dynamic sql

DECLARE @DatabaseName varchar(150) = 'dbamaint'
declare @sqltext nvarchar(max) = N''

set @sqltext = N'CREATE DATABASE '+quotename(@DatabaseName)+ ';'

print @sqltext 

-- once you are happy .. uncomment below
--exec sp_executesql @sqltext
set @sqltext = ''
set @sqltext = N'use '+quotename(@DatabaseName)+ ';'
print @sqltext 
-- once you are happy .. uncomment below
--exec sp_executesql @sqltext

below is the output of print .. once you uncomment the exec sp_executesql @sqltext the statements will be actually executed ...

CREATE DATABASE [dbamaint];
use [dbamaint];