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)
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
Thank you for this very simple article! Your articles always bring me back to simplicity to solve problems!
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
there is even a 3th method:
DECLARE @Var1 INT = 11, @Var2 INT = 22
SELECT @Var1 ‘Var1’, @Var2 ‘Var2’
GO