SQL SERVER – DECLARE Multiple Variables in One Statement

Just a day ago, while I was enjoying mini vacation during festival of Diwali I met one of the .NET developer who is big fan of Oracle. While discussing he suggested that he wished SQL Server should have feature where multiple variable can be declared in one statement. I requested him to not judge wonderful product like SQL Server with just one feature.

SQL Server is great product and it has many feature which are very unique to SQL Server. Regarding feature of SQL Server where multiple variable can be declared in one statement, it is absolutely possible to do.

Method 1: Multiple statements for declaring multiple variables

DECLARE @Var1 INT
DECLARE
@Var2 INT
SET
@Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

Method 2: Single statements for declaring multiple variables

DECLARE @Var1 INT, @Var2 INT
SET
@Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

From above example it is clear that multiple variables can be declared in one statement. In SQL Server 2008 when variables are declared they can be assigned values as well. Please refer my previous article for the same SQL SERVER – 2008 – Two Convenient Features Inline Assignment – Inline Operations.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Quest

Best Practices, SQL Scripts
Previous Post
SQLAuthority News – Download Microsoft SQL Server Management Pack for Operations Manager 2007
Next Post
SQL SERVER – Stored Procedure WITH ENCRYPTION and Execution Plan

Related Posts

31 Comments. Leave new

  • hi pinal,
    i have one problem with my query.it will work fine but i want suppose i will add some columns in table then the query will be automatically change means in this query i don’t want use hard core values as i use.Is any way for that.

    Thanks,
    Datta

    Reply
  • Curtis A DeHart II
    August 13, 2014 7:47 am

    Thank you for this very simple article! Your articles always bring me back to simplicity to solve problems!

    Reply
  • Emmanuel Abraham
    September 6, 2017 3:44 am

    Hi, I want to get this query right ( please consider the declare statement for the 3 months I want to achieve) so I could use pivot to obtain a report. For now this is giving me error message. Please what am I missing in the syntax ?
    The result is right if I declare for one month but error shows up when I try to declare for 3 months.
    Thanks.

    DECLARE
    @StartDate DATE =’01 Jun 2017′,
    @EndDate DATE = ’30 June 2017′
    @StartDate DATE = ’01 Jul 2017′,
    @EndDate DATE =’31 Jul 2017′
    @StartDate DATE = ’01 Aug 2017′,
    @EndDate DATE = ’31 Aug 2017′

    SELECT
    AccountNumber,
    UnitNumber,
    ArrivalDateTime,
    DepartureDateTime,
    BreachReason,
    HospitalName,
    ReasonForVisit,
    IPAccountNumber,
    DOB,
    DATEDIFF(YEAR,DOB,ArrivalDateTime) AS Age,
    DATEDIFF(HOUR,ArrivalDateTime,DepartureDateTime) AS [Hour],
    CASE WHEN DATEDIFF(HOUR,ArrivalDateTime,DepartureDateTime) <=4 THEN 'Not Breach'
    ELSE 'Breach' END AS BreachStatus,
    CASE
    WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 0 AND 10 THEN '[0-10]'
    WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 11 AND 20 THEN '[11-20]'
    WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 21 AND 30 THEN '[21-30]'
    WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 31 AND 40 THEN '[31-40]'
    WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 41 AND 50 THEN '[41-50]'
    WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 51 AND 60 THEN '[51-60]'
    WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 61 AND 70 THEN '[61-70]'
    ELSE '70+'END AS AgeGroup
    FROM DigitalInsightHealthTrustDataWareHouse.Activity.AandE
    WHERE CAST(ArrivalDateTime AS DATE) BETWEEN @StartDate AND @EndDate

    Reply
  • there is even a 3th method:
    DECLARE @Var1 INT = 11, @Var2 INT = 22
    SELECT @Var1 ‘Var1’, @Var2 ‘Var2’
    GO

    Reply

Leave a Reply