Declaring multiple variable in T_SQL

Question

I had the following declaration:

DECLARE @orderGUID VARCHAR(50) = NEWID(),
    @deliveryId VARCHAR(50) = NEWID(),
    @orderChildGUID VARCHAR(50) = NEWID(),
    @deliveryIdChild VARCHAR(50) = NEWID(),
    @triggerFileName VARCHAR(50) = 'trigger_[X].txt',
    @triggerTime TIME = GETDATE(),
    @triggerDate DATE = GETDATE(),
    @dateTimeText CHAR(17) = rfa.dbo.FormatDate(@triggerDate, 'YYYYMMDD') 
     + LEFT(REPLACE(REPLACE(@triggerTime,':',''),'.',''),9);

Which gives the following error: Must declare the scalar variable "@triggerDate"

I tried this and it works but just doesn't seem as nice:

DECLARE @triggerTime TIME = GETDATE(),
        @triggerDate DATE = GETDATE();
DECLARE @orderGUID VARCHAR(50) = NEWID(),
        @deliveryId VARCHAR(50) = NEWID(),
        @orderChildGUID VARCHAR(50) = NEWID(),
        @deliveryIdChild VARCHAR(50) = NEWID(),
        @triggerFileName VARCHAR(50) = 'trigger_[X].txt',
        @dateTimeText CHAR(17) = rfa.dbo.FormatDate(@triggerDate, 'YYYYMMDD') 
         + LEFT(REPLACE(REPLACE(@triggerTime,':',''),'.',''),9);

Is it possible to do this with only one declaration?

No. You cannot use a variable before it has been declared. None of the variables in a DECLARE statement are declared until the statement completes execution, so the last expression in your DECLARE cannot be compiled. — RBarryYoung
– RBarryYoung, Commented Feb 26, 2014 at 15:43
Why are you storing GUIDs as VARCHAR(50) ??? Why not use the proper datatype of UNIQUEIDENTIFIER????? — marc_s
– marc_s, Commented Feb 26, 2014 at 16:07
TBH this is the first I have heard of UNIQUEIDENTIFIER thanks! — Timigen
– Timigen, Commented Feb 26, 2014 at 17:52

Community · Accepted Answer · 2017-05-23 12:20:57Z

2

No.

Exactly as rbarryyoung wrote in his comment.

As an aside, I tend to prefer the "extra" DECLARE especially during development. I find it much easier to follow what is being declared and to add, removed, or reorder declarations if they are each individual. This to me is worth the repetition of declare.

DECLARE @triggerDate DATE = GETDATE()
DECLARE @triggerTime TIME = GETDATE()
DECLARE @orderGUID VARCHAR(50) = NEWID()
DECLARE @deliveryId VARCHAR(50) = NEWID()
DECLARE @orderChildGUID VARCHAR(50) = NEWID()
DECLARE @deliveryIdChild VARCHAR(50) = NEWID()
DECLARE @triggerFileName VARCHAR(50) = 'trigger_[X].txt'
DECLARE @dateTimeText CHAR(17) = rfa.dbo.FormatDate(@triggerDate, 'YYYYMMDD') + LEFT(REPLACE(REPLACE(@triggerTime,':',''),'.',''),9)

I don't believe there is any performance difference between the two styles, but someone might be able to show one being faster in some cases. That would be interesting.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Feb 26, 2014 at 16:00

Karl Kieninger

9,1692 gold badges36 silver badges49 bronze badges

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

Comments

sunk · Accepted Answer · 2014-02-26 15:52:21Z

2

Separate declaration is required:-

DECLARE @triggerDate DATE = GETDATE(), @triggerTime TIME = GETDATE()
DECLARE @orderGUID VARCHAR(50) = NEWID(),
        @deliveryId VARCHAR(50) = NEWID(),
        @orderChildGUID VARCHAR(50) = NEWID(),
        @deliveryIdChild VARCHAR(50) = NEWID(),
        @triggerFileName VARCHAR(50) = 'trigger_[X].txt',
        @dateTimeText CHAR(17) = rfa.dbo.FormatDate(@triggerDate, 'YYYYMMDD') + LEFT             (REPLACE(REPLACE(@triggerTime,':',''),'.',''),9)

answered Feb 26, 2014 at 15:52

sunk

3201 gold badge3 silver badges9 bronze badges

Collectives™ on Stack Overflow

Declaring multiple variable in T_SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related