SQL SERVER 2008 – 2012 – Declare and Assign Variable in Single Statement

Many of us are tend to overlook simple things even if we are capable of doing complex work. In SQL Server 2008, inline variable assignment is available. This feature exists from last 3 years, but I hardly see its utilization. One of the common arguments was that as the project migrated from the earlier version, the feature disappears. I totally accept this argument and acknowledge it. However, my point is that this new feature should be used in all the new coding – what is your opinion?

The code which we used in SQL Server 2005 and the earlier version is as follows:

DECLARE @iVariable INT, @vVariable VARCHAR(100), @dDateTime DATETIME
SET
@iVariable = 1
SET @vVariable = 'myvar'
SET @dDateTime = GETDATE()
SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT
GO

Solarwinds

The same should be re-written as following:

DECLARE @iVariable INT = 1, @vVariable VARCHAR(100) = 'myvar', @dDateTime DATETIME = GETDATE()
SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT
GO

I have started to use this new method to assign variables as I personally find it very easy to read as well write.

Do you still use the earlier method to declare and assign variables? If yes, is there any particular reason or just an old routine? I am interested to hear about this.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Tell me What You Want to Listen – My 2 TechED 2011 Sessions
Next Post
SQL SERVER – Log File Growing for Model Database – model Database Log File Grew Too Big

Related Posts

15 Comments. Leave new

  • This is a simple but interesting topic Pinal. I wrote about this sometime back over my blogs too – .. There are some hidden things that can get missed too.

    Nevertheless, it is a lerning opportunity everytime :).

    Vinod

    Reply
    • Wow,

      I never thought of checking profile and event – your link is indeed great read. Going next level.

      Reply
      • Charles Hogle
        June 30, 2014 4:10 am

        I’m trying to find a way to Create one datetime variables and one int variable. The int variable will represent minutes
        — Store 1/10/2012 in the date variable and 25,000 in the integer variable.
        — Use a function to Add the int variable to the date variable returning a new date.
        — Use the Select statement to display the value of the results of adding all three variables.
        — Need help check out the date time functions found in the Object Explorer.
        could you help me out

  • Varinder Sandhu
    March 18, 2011 12:28 pm

    Thanks Pinal,

    This is really a very helpful information

    Reply
  • I follow the traditional/old method. Declare with initializer seems interesting will have to apply it and see how it works out.

    Reply
  • Nakul Vachhrajani
    March 18, 2011 10:47 pm

    Hello, Pinal!

    We use in-line declaration and initialization for any new code that we write. As you mentioned, our excuse for not going back and rectifying old code is simply “If it isn’t broken, don’t go and fix it”. However, all our checklists have been updated to check for usage of newer features whenever a particular piece of code is modified.

    However, for data cleansing scripts which remain the same for some versions of our product (and if these versions also run on older SQL Server versions), we use the conventional methods of declaration & initialization.

    Word of caution though – this feature might cause developers to declare and use variables as they think about them (i.e. in the middle of the script). That is one practice that should be avoided.

    Vinod: Thank-you for the link to your post. It was great reading!

    To the readers: If you haven’t been to Vinod’s post, please go there now – it gives you a very good reason why declaration & assignment also need to be part of the TRY…CATCH block.

    Reply
  • Marie Haggberg
    August 10, 2011 11:45 pm

    I find traditional declaration and initialization easier visually, especially in a team environment where you may be working with unfamiliar code. Discrete statements “stand out” more, as would headings in a written document.

    At the same time, I can appreciate the utility of this approach, and see instances where it would be more robust, e.g. inclusion in a try/catch block as mentioned above.

    Thanks for the good post.

    Reply
  • DECLARE @iVariable INT = 1, @vVariable VARCHAR(100) = ‘myvar’, @dDateTime DATETIME = GETDATE()
    SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT
    GO

    Not working in SQL SErver 2005 Getting the error “Cannot assign a default value to a local variable. ”

    Please update your post…

    Reply
  • thanks ver good

    Reply
  • Hi i created a temp table but i want to create for below code

    DECLARE @StartDate DATETIME
    ,@EndDate DATETIME;
    SELECT @StartDate =’20100101′

    ,@EndDate = GETDATE()
    –convert(varchar(8), GETDATE(),112);

    ;With DT_TempTbl as
    (
    SELECT @StartDate AS YearMonth
    UNION ALL
    SELECT DATEADD(MONTH, 1, YearMonth) FROM DT_TempTbl
    WHERE YearMonth <=@EndDate
    )

    select convert(varchar(6),YearMonth,112) AS YearMonth1 FROM DT_TempTbl order by YearMonth1

    Please help me to creating function

    Reply
  • Hello,

    I have a requirements like I have 4 variable and want the result like var1 and var2….

    If any variable is not having any value, it should skip that part. How I can do this?

    Reply
  • I had been doing the old way as I never knew you could do that until the other day.

    Reply

Leave a Reply

Menu