T-SQL create table statement will not accept variable

Question

Why can I not use a variable to name a new table?

As a beginning SQL project, I'm making a personal finance database. Each account will have a corresponding table in the database. There is also a table listing all the current accounts. See (simplified) code sample below:

CREATE TABLE accountList
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [Name] NCHAR(30) NOT NULL UNIQUE,
    [Active] BIT NOT NULL
)

INSERT INTO accountList(name, active)
VALUES
     ('Bank_One_Checking', 1);  

CREATE TABLE Bank_One_Checking
(
[Id] BIGINT NOT NULL PRIMARY KEY IDENTITY, 
    [payee] NCHAR(30) NOT NULL UNIQUE, 
    [category] NCHAR(30) NOT NULL UNIQUE, 
[amount] INT NOT NULL DEFAULT 0.00
)

This code works. I want to set the account name to a variable (so it can be passed as a parameter to a stored procedure). See code below:

DECLARE @accountName nchar(30);
SET @accountName = 'Bank_One_Savings';

INSERT INTO accountList(name, active)
VALUES
    (@accountName, 1);

CREATE TABLE @accountName
(
    [Id] BIGINT NOT NULL PRIMARY KEY IDENTITY, 
    [payee] NCHAR(30) NOT NULL UNIQUE, 
    [category] NCHAR(30) NOT NULL UNIQUE, 
    [amount] INT NOT NULL DEFAULT 0.00
)

Line 6 in that code (CREATE TABLE @accountName) produces an error

Incorrect syntax near @accountName, expecting '.', 'ID', or 'QUOTEID'.

Why won't it insert the variable into the command?

you cannot use a variable for table and column names, for that you need to use dynamic sql — Roy
– Roy, Commented Mar 2, 2016 at 2:51
Don't do this. It's bad design. That's why it's hard to do. You should have an Accounts table, and a Transactions table: each row of the Transactions table should reference an Account.ID. — Blorgbeard
– Blorgbeard, Commented Mar 2, 2016 at 2:51
You cannot use a variable for the table name in queries. A better option would be to have one table and a column to distinguish the bank. Mutliple tables with the same structure and purpose is generally a bad design. — D Stanley
– D Stanley, Commented Mar 2, 2016 at 2:52
Thanks a bunch for the advice. It's interesting how what looks like a bug in the code can reveal a fundamental flaw in design. — Bagheera
– Bagheera, Commented Mar 2, 2016 at 14:28

Nimantha · Accepted Answer · 2022-01-03 12:08:22Z

4

SQL doesn't allow tables to be variables. You could use dynamic SQL, if you like, but I strongly recommend against it.

Your code has several flaws. You should learn not only to fix them but why they are wrong.

You need a "master" table, where AccountName is a column. Multiple tables with the same structure is almost always a sign of poor database design.
Strings should be designed using VARCHAR() or NVARCHAR(), unless they are short or known to be the same length (say an account number that is always 15 characters). Fixed-length strings just waste space.
I find it unlikely that a column named category would be unique in such a table. It seems to violate what uniqueness means.
Integers are not appropriate for monetary amounts in most of the world (use decimal or money). And, they shouldn't be initialized to constants with a decimal point.

edited Jan 3, 2022 at 12:08

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Mar 2, 2016 at 2:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL create table statement will not accept variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related