How to run a create table statement from a variable?

Question

Hi,

I have the following statement which runs fine:

CREATE TABLE DL.erp.FMP09DTA_ADS_2( [ADDLIN] CHAR(1) NOT NULL , [ADBUSY] CHAR(3) NOT NULL , 
[TRNSCD] CHAR(6) NOT NULL , [EMPLNO] CHAR(10) NOT NULL , [SHIFT] NUMERIC(1,0) NOT NULL , 
[AUDNOFILL] CHAR(2) NOT NULL , [AUDNO] DECIMAL(9,0) NOT NULL , [AUDSEQ] NUMERIC(4,0) NOT NULL ,
[ABHR] DECIMAL(5,2) NOT NULL , [ABCD] CHAR(3) NOT NULL , [AUCD] CHAR(4) NOT NULL , [RSCD] CHAR(4) NOT NULL , 
[AUIN] CHAR(1) NOT NULL , [PYIN] CHAR(1) NOT NULL , [HLIN] CHAR(1) NOT NULL , [HLTP] CHAR(1) NOT NULL , [INBSIN] CHAR(1) NOT NULL ,
[SCIN] CHAR(1) NOT NULL , [SCTP] CHAR(1) NOT NULL , [TRS1CD] CHAR(4) NOT NULL , [TRS2CD] CHAR(4) NOT NULL , [TRS3CD] CHAR(4) NOT NULL ,
[TRS4CD] CHAR(4) NOT NULL , [ADSHFS] CHAR(1) NOT NULL , [ADSSTM] CHAR(1) NOT NULL , [ADSAMN] DECIMAL(5,0) NOT NULL , [ADSDATE] DATE NOT NULL ,
[CNTRRN] BIGINT NOT NULL , [REPDTE] DATETIME NOT NULL , [LakeGUID] UNIQUEIDENTIFIER NULL ,
CONSTRAINT PK_Notes27 PRIMARY KEY  CLUSTERED (EMPLNO ASC,SHIFT ASC,AUDNO ASC,ADSDATE ASC,CNTRRN ASC))
CREATE NONCLUSTERED INDEX [ix_FMP09DTA_ADS_2_LakeGUID] ON DL.erp.FMP09DTA_ADS_2 (LakeGUID ASC)

But when I run it like this:

declare @s nvarchar(max)
= 'CREATE TABLE DL.erp.FMP09DTA_ADS_2( [ADDLIN] CHAR(1) NOT NULL , [ADBUSY] CHAR(3) NOT NULL , [TRNSCD] CHAR(6) NOT NULL , [EMPLNO] CHAR(10) NOT NULL , [SHIFT] NUMERIC(1,0) NOT NULL , [AUDNOFILL] CHAR(2) NOT NULL , [AUDNO] DECIMAL(9,0) NOT NULL , [AUDSEQ] NUMERIC(4,0) NOT NULL , [ABHR] DECIMAL(5,2) NOT NULL , [ABCD] CHAR(3) NOT NULL , [AUCD] CHAR(4) NOT NULL , [RSCD] CHAR(4) NOT NULL , [AUIN] CHAR(1) NOT NULL , [PYIN] CHAR(1) NOT NULL , [HLIN] CHAR(1) NOT NULL , [HLTP] CHAR(1) NOT NULL , [INBSIN] CHAR(1) NOT NULL , [SCIN] CHAR(1) NOT NULL , [SCTP] CHAR(1) NOT NULL , [TRS1CD] CHAR(4) NOT NULL , [TRS2CD] CHAR(4) NOT NULL , [TRS3CD] CHAR(4) NOT NULL , [TRS4CD] CHAR(4) NOT NULL , [ADSHFS] CHAR(1) NOT NULL , [ADSSTM] CHAR(1) NOT NULL , [ADSAMN] DECIMAL(5,0) NOT NULL , [ADSDATE] DATE NOT NULL , [CNTRRN] BIGINT NOT NULL , [REPDTE] DATETIME NOT NULL , [LakeGUID] UNIQUEIDENTIFIER NULL ,CONSTRAINT PK_Notes2 PRIMARY KEY CLUSTERED (EMPLNO ASC,SHIFT ASC,AUDNO ASC,ADSDATE ASC,CNTRRN ASC)) CREATE NONCLUSTERED INDEX [ix_FMP09DTA_ADS_2_LakeGUID] ON DL.erp.FMP09DTA_ADS_2 (LakeGUID ASC)'

exec @s

I get the error:

The name 'CREATE TABLE DL.erp.FMP09DTA_ADS_2( [ADDLIN] CHAR(1) NOT NULL , [ADBUSY] CHAR(3) NOT NULL , [TRNSCD] CHAR(6) NOT NULL , [EMPLNO] CHAR(10) NOT NULL , [SHIFT] NUMERIC(1,0) NOT NULL , [AUDNOFILL] CHAR(2) NOT NULL , [AUDNO] DECIMAL(9,0) NOT NULL , [AUDSEQ] NUMERIC(4,0) NOT NULL , [ABHR] DECIMAL(5,2) NOT NULL , [ABCD] CHAR(3) NOT NULL , [AUCD] CHAR(4) NOT NULL , [RSCD] CHAR(4) NOT NULL , [AUIN] CHAR(1) NOT NULL , [PYIN] CHAR(1) NOT NULL , [HLIN] CHAR(1) NOT NULL , [HLTP] CHAR(1) NOT NULL , [INBSIN] CHAR(1) NOT NULL , [SCIN] CHAR(1) NOT NULL , [SCTP] CHAR(1) NOT NULL , [TRS1CD] CHAR(4) NOT NULL , [TRS2CD] CHAR(4) NOT NULL , [TRS3CD] CHAR(4) N' is not a valid identifier.

Any ideas what i'm doing wrong?

Thanks

Answer 1

A simple EXEC don't work here, use sp_executesql instead =>

exec sp_executesql @s;

Answer 2

What you actually did wrong was to omit the parentheses.

EXEC @s -- Executes the stored procedure of which the name is in @s.
EXEC(@s) -- Executes the SQL code in @s.

Answer 3

Thanks Olaf! That worked