question

zoeOhara-0533 avatar image
0 Votes"
zoeOhara-0533 asked ErlandSommarskog answered

How to run a create table statement from a variable?

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


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

 exec sp_executesql @s;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

zoeOhara-0533 avatar image
0 Votes"
zoeOhara-0533 answered

Thanks Olaf! That worked

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.