Error declaring multiple table variables in SQL

Question

I have problem declaring multible table variables in Microsoft SQL Server. Trying to separate the tables with comma but seems to give me some syntax errors.

Code looks like this. Show both code as sample and as a picture in order for you to see the error marks:

DECLARE
@StructuredProducts TABLE(
    StructuredProductId INT
    ,ArrangerIdv2 INT
    ,ISIN VARCHAR(50)
    ,InstrumentId INT
    ,Caption VARCHAR(100)
    ,DbRegDate DATE
    ,EndDate DATE
),
@PriceDataOld TABLE(
    StructuredProductId INT
    ,FinalDate DATE
    ,FinalPriceDate DATE
    ,FinalPrice DECIMAL(9,3)
    ,FinalPriceSek DECIMAL(9,3)
),
@PriceDataEarlyExercise TABLE(
    StructuredProductId INT
    ,EEDate DATE
    ,EEPriceDate DATE
    ,EEPrice DECIMAL(9,3)
    ,EEPriceSek DECIMAL(9,3)
),
@PriceDataActive TABLE(
    StructuredProductId INT
    ,ActiveDate DATE
    ,ActivePriceDate DATE
    ,ActivePrice DECIMAL(9,3)
    ,ActivePriceSek DECIMAL(9,3)
)

Want the code to be as neat as possible. Isn't comma the way to declare multiple variables? — David
– David, Commented Mar 1, 2019 at 10:02
@David tables and variables are completely different please check the syntax — Chanukya
– Chanukya, Commented Mar 1, 2019 at 10:04

AakashM · Accepted Answer · 2019-03-01 10:08:02Z

8

To address your comment

Isn't comma the way to declare multiple variables?

well, yes a comma can be used to declare multiple local variables and cursors, but not table variables - the docs explicitly say this:

n

Is a placeholder indicating that multiple variables can be specified and assigned values. When declaring table variables, the table variable must be the only variable being declared in the DECLARE statement.

answered Mar 1, 2019 at 10:08

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

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

Comments

Chanukya · Accepted Answer · 2019-03-01 10:01:16Z

1

SQL server we have to create table like this format only there is no other styles 

 DECLARE @StructuredProducts TABLE(
StructuredProductId INT
,ArrangerIdv2 INT
,ISIN VARCHAR(50)
,InstrumentId INT
,Caption VARCHAR(100)
,DbRegDate DATE
,EndDate DATE
) ;
DECLARE @PriceDataOld TABLE(
StructuredProductId INT
,FinalDate DATE
,FinalPriceDate DATE
,FinalPrice DECIMAL(9,3)
,FinalPriceSek DECIMAL(9,3)
);
DECLARE @PriceDataEarlyExercise TABLE(
StructuredProductId INT
,EEDate DATE
,EEPriceDate DATE
,EEPrice DECIMAL(9,3)
,EEPriceSek DECIMAL(9,3)
);
DECLARE @PriceDataActive TABLE(
StructuredProductId INT
,ActiveDate DATE
,ActivePriceDate DATE
,ActivePrice DECIMAL(9,3)
,ActivePriceSek DECIMAL(9,3)
);

answered Mar 1, 2019 at 10:01

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-01 10:02:27Z

1

change it like below

DECLARE
@StructuredProducts TABLE(
    StructuredProductId INT
    ,ArrangerIdv2 INT
    ,ISIN VARCHAR(50)
    ,InstrumentId INT
    ,Caption VARCHAR(100)
    ,DbRegDate DATE
    ,EndDate DATE
);DECLARE
@PriceDataOld TABLE(
    StructuredProductId INT
    ,FinalDate DATE
    ,FinalPriceDate DATE
    ,FinalPrice DECIMAL(9,3)
    ,FinalPriceSek DECIMAL(9,3)
);DECLARE
@PriceDataEarlyExercise TABLE(
    StructuredProductId INT
    ,EEDate DATE
    ,EEPriceDate DATE
    ,EEPrice DECIMAL(9,3)
    ,EEPriceSek DECIMAL(9,3)
);DECLARE
@PriceDataActive TABLE(
    StructuredProductId INT
    ,ActiveDate DATE
    ,ActivePriceDate DATE
    ,ActivePrice DECIMAL(9,3)
    ,ActivePriceSek DECIMAL(9,3)
)

answered Mar 1, 2019 at 10:02

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Collectives™ on Stack Overflow

Error declaring multiple table variables in SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related