CODEWITHSUNDEEP
sql-servert-sql
Timigen
Timigen

Reputation: 1025

Declaring multiple variable in T_SQL

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?

Upvotes: 0

Views: 516

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9147

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.

Upvotes: 2

sunk
sunk

Reputation: 320

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)

Upvotes: 2

Related Questions