CODEWITHSUNDEEP
sql-server
M.G.
M.G.

Reputation: 133

SQL Server : create table using variables

When trying to execute the following, I get the errors

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'GO'.

Can someone tell me where I am going wrong? I don't understand how the syntax is wrong 

DECLARE @table nvarchar(100);
DECLARE @sql nvarchar(max);

SET @table = 'FooTable';

SET @sql = N'CREATE TABLE [dbo].[' + @table + '](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [AddedBy] [int] NOT NULL,
    [AddedDate] [datetime2](7) NOT NULL,
CONSTRAINT [PK_' + @table + '] PRIMARY KEY CLUSTERED 
( [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[' + @table + '] ADD  CONSTRAINT [DF_' + @table + '_AddedBy]  DEFAULT ((-1)) FOR [AddedBy]
GO
ALTER TABLE [dbo].[' + @table + '] ADD  CONSTRAINT [DF_' + @table + '_AddedDate]  DEFAULT (getutcdate()) FOR [AddedDate]
GO';

exec (@sql)

Thanks for your assistance.

Upvotes: 0

Views: 76

Answers (2)

jpw
jpw

Reputation: 44871

The syntax would be fine if you executed the code inside the management tool which understands the GO command as a batch separator (just like the isql and osql tools). When you execute the code using exec() the GO command is not understood, which is why you get the error.

The solution is to either remove the GO statements (or replace them with ; which ends a statement), and it will execute fine, or inline the constraints and skip the alter table statements altogether (which looks cleaner in my opinion):

SET @sql = 
N'CREATE TABLE [dbo].[' + @table + '](
    [id] int IDENTITY(1,1) NOT NULL,
    [AddedBy] int NOT NULL CONSTRAINT [DF_' + @table + '_AddedBy]  DEFAULT ((-1)),
    [AddedDate] datetime2(7) NOT NULL CONSTRAINT [DF_' + @table + '_AddedDate]  DEFAULT (getutcdate()) ,
    CONSTRAINT [PK_' + @table + '] PRIMARY KEY CLUSTERED ( [id] ASC ) 
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
       ) ON [PRIMARY]
) ON [PRIMARY]
';

Also, there is really no need to use quoted identifiers with the types (or even the column names), so you might as well remove the brackets [].

Upvotes: 1

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

SQL server is not happy about sending batches in a dynamic context using exec(). So forget about GO. Just slice up your query where GO should be and exec() slices one by one:

DECLARE @table nvarchar(100);
DECLARE @sql nvarchar(max);

SET @table = 'FooTable';

SET @sql = N'CREATE TABLE [dbo].[' + @table + '](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [AddedBy] [int] NOT NULL,
    [AddedDate] [datetime2](7) NOT NULL,
CONSTRAINT [PK_' + @table + '] PRIMARY KEY CLUSTERED 
( [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
exec (@sql)

SET @sql = 'ALTER TABLE [dbo].[' + @table + '] ADD  CONSTRAINT [DF_' + @table + '_AddedBy]  DEFAULT ((-1)) FOR [AddedBy]'
exec (@sql)

SET @sql = 'ALTER TABLE [dbo].[' + @table + '] ADD  CONSTRAINT [DF_' + @table + '_AddedDate]  DEFAULT (getutcdate()) FOR [AddedDate]'
exec (@sql)

Upvotes: 1

Related Questions