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
@iVariable = 1
SET @vVariable = 'myvar'
SET @dDateTime = GETDATE()
SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT

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

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 (http://blog.SQLAuthority.com)

17 thoughts on “SQL SERVER 2008 – 2012 – Declare and Assign Variable in Single Statement

      • 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


  1. 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.


  2. Pingback: SQL SERVER – SQL Server Migration Assistant (SSMA) – Tools – Video – Download Journey to SQLAuthority

    • Hi Pinal,

      Question about Assign variable

      I want to create a function, or scalar variable or good knowns what to have a assign variable to simulate a CNAME. We have couple function that they hardcode another server or another database.


      CREATE FUNCTION [dbo].[fn_GetCNAME]


      DECLARE @@Globalvariabe varchar(50)
      set @@Globalvariabe = ‘SERVERNAME1’

      when I use a function, I can not put e CNAME, but the result of function or scalar function>

      CREATE FUNCTION [dbo].[fn_GeNAME]
      SELECT *
      FROM [fn_GetCNAME()].db1.dbo.NAME

      I want to simulate un reel assign, when the SQL Server service started.



  3. 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.


  4. Pingback: SQL SERVER – T-SQL Constructs – Declaration and Initialization – SQL in Sixty Seconds #003 – Video « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – T-SQL Constructs – *= and += – SQL in Sixty Seconds #009 – Video « SQL Server Journey with SQL Authority

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

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

    Please update your post…


  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

  8. Hi i created a temp table but i want to create for below code

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

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

    ;With DT_TempTbl as
    SELECT @StartDate AS YearMonth
    WHERE YearMonth <=@EndDate

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

    Please help me to creating function


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s