19

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
  • You need to use dynamic sql for that. mssqltips.com/sqlservertip/1160/… – Stijn Wynants Dec 12 '16 at 15:55
  • 3
    Hey thanks for commenting but as per my question, this isn't the answer I am looking for. I want to know if anyone knows why I can't do it like I have shown. – gareth Dec 12 '16 at 15:59
  • 2
    Because it is not possible to use the USE command with a parameter. – TT. Dec 12 '16 at 16:37
  • 2
    In addition to the answers already given, but not sufficient for an answer of its own, variables are scoped to a maximum of the current batch. (I don't know off hand if it is possible to scope variables more narrowly than that in SQL Server.) So the moment you have the GO, the previously declared variable disappears. You may want to look into SQLCMD variables, which may or may not be applicable to your scenario. – a CVn Dec 12 '16 at 20:42
  • 1
    We tend to think of database names and column names as string values, but in the context of SQL they are Identifiers. What you're attempting would be the same as expecting x = 7; to be the same as 'x' = 7; in some other language. Just as a computer language could be created that deals with 'x'=7 the same as x=7, an RDBMS could be created that treats Create Table X the same as Create Table 'X'. But that wouldn't be SQL. – user1008646 Dec 13 '16 at 12:30
21

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;
| improve this answer | |
  • 3
    Note that dynamic SQL should be avoided whenever possible. It's the SQL analog to using eval functions in procedural languages like JavaScript and Python. It is a quick way to create security holes. – jpmc26 Dec 12 '16 at 22:39
  • 1
    @jpmc26: What is the more secure way to do it that doesn't involve dynamic SQL? – Robert Harvey Dec 13 '16 at 17:06
  • 1
    @RobertHarvey Just because it's best avoided doesn't mean there's always an alternative with exactly the same functionality. ;) Often times, part of the answer is, "Use a completely different solution to the problem." Sometimes it is the best thing to do, but not without a good amount of deliberating and making sure you haven't neglected alternatives, and even then, it should come with a healthy dose of caution. – jpmc26 Dec 13 '16 at 17:12
  • 2
    @jpmc26: The OP's example looks like what a "Code-First" ORM might do to set up tables in a database. While dynamic SQL is insecure in principle, the end-user would never touch that particular code. – Robert Harvey Dec 13 '16 at 17:15
  • @RobertHarvey Depends who you consider the "end user." For a script that deploys a DB, I'd consider the developer and possibly some sys admins to be the "end user." I'd still design to system to reject insecure input in that case, if for no other reason than to avoid accidents. Also, as for "never touch," the OP is touching this code, so... – jpmc26 Dec 13 '16 at 17:23
17

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!

| improve this answer | |
7

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.

| improve this answer | |
2

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];
| improve this answer | |
  • 1
    Yes thanks, I know that, but I want to know if anyone knows why you cant just use the variable? – gareth Dec 12 '16 at 15:58
  • The T-SQL parser will throw syntax errors. Its not a valid T-SQL that the parser recognizes. – Kin Shah Dec 12 '16 at 16:00
  • Thanks Kin, I am sure there must be good reasons for it. Maybe because database names can contain '@' and probably some other more complex reasons. – gareth Dec 12 '16 at 16:03
  • 1
    Yes, they can contain @ and i think it's main reason. msdn.microsoft.com/en-us/library/ms175874.aspx – Paweł Tajs Dec 12 '16 at 16:04
  • 1
    @gazeranco Believe me, anybody that works with SQL Server no doubt wishes that more commands would accept variables in place of constant identifiers. – db2 Dec 12 '16 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.