دستور Case در SQL Server – قسمت اول

مقدمه

در این مقاله آموزشی هدفم این است که دوستان عزیر را با عبارت Case آشنا کنم.
Case می‌تواند در دستورات TSQL مورد استفاده قرار گیرد و باعث شود که ارزیابی به ازای یکی از حالت‌ها و شرایط ممکن در آن برای عبارت TSQL بوجود آید.

شکل کلی یک دستور Case در SQL Server

۱- Simple Case Expression (ساده):

در این نوع Case عبارت شما با یک یا چند مقدار بررسی شده و در صورت برابری با هر کدام از مقادیر ارزش مربوط آن برگردانده خواهد شد.
شکل کلی این نوع Case به صورت زیر می‌باشد:

CASE Expression
WHEN Expression1 THEN Result1
WHEN Expression2 THEN Result2
ELSE ResultN
END

مثال : در این مثال هدف این است کد یک کارمند را با تعدادی عبارت مقایسه کنیم و در صورت برابری با هر کدام از عبارت‌های ارزش مربوط به آن را نمایش دهیم.

CASE Expression
WHEN Expression1 THEN Result1
WHEN Expression2 THEN Result2
ELSE ResultN
END

DECLARE @EmpCode INT=200
SELECT
CASE @EmpCode
WHEN 100 THEN N'مسعود طاهری'
WHEN 200 THEN N'فرید طاهری'
WHEN 300 THEN N'احمد غفاری'
WHEN 400 THEN N'مجید طاهری'
ELSE N'فاقد کد'
END
GO

۲- Searched Case Expression:

در این مدل از دستور Case عبارت مورد نظر شما با یک یا چند عبارت از نوع Boolean بررسی می‌شود و در صورتیکه هر کدام از عبارت‌ها True شود ارزش مربوط به آن برگردانده خواهد شد.
شکل کلی این نوع Case به صورت زیر می‌باشد.

CASE
WHEN Boolean_Expression1 THEN Result1
WHEN Boolean_Expression2 THEN Result2
ELSE ResultN
END

مثال : در این مثال هدف این است که مقدار یک متغییر را با Range‌ از اعداد بررسی کنیم.

DECLARE @V1 INT=12
SELECT
CASE
WHEN @V1 <=10 THEN N'ضعیف'
WHEN @V1 BETWEEN 11 AND 15 THEN N'متوسط'
WHEN @V1 BETWEEN 16 AND 20 THEN N'خوب'
ELSE N'---'
END

نکته مهمی که درباره دستور Case در SQL وجود دارد این است که یک عبارت Case را می‌توان توسط انواع دستورات TSQL مورد استفاده قرار داد. در ادامه مقاله نمونه‌ای از کاربرد Case به همراه دستورات TSQL را بررسی خواهیم کرد.
برای اینکه بتوانیم مثال‌های را به بهترین نحو ممکن بررسی کنیم جدول Employees را در بانک اطلاعاتی Tempdb (این بانک یک بانک اطلاعاتی موقت بوده و به عنوان چرکنویس برای SQL Server در نظر گرفته می‌شود) ایجاد می‌کنیم.

USE tempdb
GO
IF NOT OBJECT_ID('Employees') IS NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FullName NVARCHAR(100),
GroupCode TINYINT,
GroupDesc NVARCHAR(100),
Salary BIGINT,
SalaryDesc NVARCHAR(100)
)
GO
--Table Value Constructor
INSERT INTO Employees(FullName,GroupCode,Salary) VALUES
(N'مسعود طاهری',۱,۵۰۰۰),
(N'فرید طاهری',۱,۴۰۰۰),
(N'احمد غفاری',۲,۳۰۰۰),
(N'مجید طاهری',۲,۲۰۰۰),
(N'خدیجه افروزنیا',۳,۱۰۰۰),
(N'علی طاهری',۴,۰)
GO
SELECT * FROM Employees
GO

کاربرد Case به همراه انواع دستورات TSQL

۱- بررسی کاربرد Case با استفاده از دستور Select

در صورتیکه نوع Case به صورت Simple Case Expression باشد.

SELECT
,E.EmployeeID
,E.FullName
CASE E.GroupCode
WHEN 1 THEN N'اول'
WHEN 2 THEN N'دوم'
WHEN 3 THEN N'سوم'
ELSE NULL
END AS GroupDesc
FROM Employees E
GO

کوئری بالا را می‌توان به شکل زیر نیز نوشت.

SELECT
E.EmployeeID,
E.FullName,
GroupDesc=CASE E.GroupCode
WHEN 1 THEN N'اول'
WHEN 2 THEN N'دوم'
WHEN 3 THEN N'سوم'
ELSE NULL
END
FROM Employees E
GO

در صورتیکه نوع Case به صورت Searched Case Expression باشد.

SELECT
,E.EmployeeID
,E.FullName
,E.Salary
CASE
WHEN E.Salary <=1000 THEN N'کم'
WHEN E.Salary >1000 AND E.Salary<=3000  THEN N'متوسط'
WHEN E.Salary BETWEEN 3001 AND 5000 THEN N'خوب'
WHEN E.Salary >5000 THEN N'بالا'
END AS SalaryDesc
FROM Employees E
GO

۲- بررسی کاربرد Case با استفاده از دستور Update

برخی از مواقع نیاز داریم مقدار یک فیلد را با توجه به شرایط خاصی Update کنیم. یکی از کارهایی که می‌توان در این حالت انجام داد استفاده از Case می‌باشد.
در صورتیکه هنگام استفاده از دستور Update نوع Case به صورت Simple Case Expression باشد می‌توان از آن بدین شکل استفاده نمود.

UPDATE Employees
SET GroupDesc =
CASE GroupCode
WHEN 1 THEN N'اول'
WHEN 2 THEN N'دوم'
WHEN 3 THEN N'سوم'
ELSE NULL
END
OUTPUT inserted.*
GO
SET GroupDesc =
CASE GroupCode
WHEN 1 THEN N'اول'
WHEN 2 THEN N'دوم'
WHEN 3 THEN N'سوم'
ELSE NULL
END
OUTPUT inserted.*
GO

لازم می‌دانم اشاره کنم که بخش Output مورد استفاده در دستور Update باعث نمایش رکوردهایی که تحت تاثیر دستور Update قرار گرفته‌اند خواهد شد.
در صورتیکه هنگام استفاده از دستور Update نوع Case به صورت Searched Case Expression باشد می‌توان از آن بدین شکل استفاده نمود.

UPDATE Employees
SET SalaryDesc =
CASE
WHEN Salary <=1000 THEN N'کم'
WHEN Salary >1000 AND Salary<=3000 THEN N'متوسط'
WHEN Salary BETWEEN 3001 AND 5000 THEN N'خوب'
WHEN Salary >5000 THEN N'بالا'
END
OUTPUT inserted.*
GO

۳- بررسی کاربرد Case با استفاده از Order By

در برخی از مواقع ممکن است بخواهیم عملیات مرتب‌سازی رکوردها را دست‌خوش تغییر داده و مرتب‌سازی سفارشی خودمان را بر روی رکوردها اعمال کنیم. (رکوردها را بر اساس شرایط خودمان مرتب کنیم).
برای انجام این کار می‌توان از Case به شکل زیر استفاده نمود.
در صورتی که هنگام مرتب‌سازی رکوردها نوع Case به صورت Simple Case Expression باشد می‌توان از آن بدین صورت استفاده نمود.

SELECT
E.EmployeeID,
E.FullName,
E.GroupCode,
E.GroupDesc
FROM Employees E
ORDER BY
CASE
WHEN E.GroupCode=2 THEN 1
WHEN E.GroupCode=3 THEN 2
WHEN E.GroupCode=1 THEN 2
ELSE 4
END
GO

همان‌طور که در تصویر بالا مشاهده می‌کنید با انجام مرتب‌سازی سفارشی، رکوردها به ترتیب گروه دوم، سوم، اول و Null به ما نمایش داده شد.
در صورتی که هنگام مرتب‌سازی رکوردها نوع Case به صورت Searched Case Expression باشد می‌توان از آن بدین صورت استفاده نمود.

SELECT
E.EmployeeID,
E.FullName,
E.Salary,
E.SalaryDesc
FROM Employees E
ORDER BY
CASE
WHEN Salary <=1000 THEN 3
WHEN Salary >1000 AND Salary<=3000 THEN 1
WHEN Salary BETWEEN 3001 AND 5000 THEN 2
WHEN Salary >5000 THEN 4
END
GO

همان‌طور که در تصویر بالا مشاهده می‌کنید با انجام مرتب‌سازی سفارشی، رکوردها به ترتیب حقوق متوسط، خوب و کم به ما نمایش داده شد.

۴- بررسی کاربرد Case با استفاده از Set

یکی از کارهای جالبی که با استفاده از دستور Set می‌توان انجام داد تخصیص مقدار به متغیر می‌باشد.
ترکیب این دستور (Set) با Case هم امکان پذیر می‌باشد.

DECLARE @V1 NVARCHAR(50)=N'تیر'
DECLARE @V2 NVARCHAR(50)
SET @V2 =
CASE @V1
WHEN N'فروردین'    THEN N'بهار'
WHEN N'اردیبهشت'   THEN N'بهار'
WHEN N'خرداد'       THEN N'بهار'
WHEN N'تیر'          THEN N'تابستان'
WHEN N'مرداد'        THEN N'تابستان'
WHEN N'شهریور'       THEN N'تابستان'
WHEN N'مهر'          THEN N'پاییز'
WHEN N'آبان'        THEN N'پاییز'
WHEN N'آذر'           THEN N'پاییز'
WHEN N'دی'            THEN N'زمستان'
WHEN N'بهمن'        THEN N'زمستان'
WHEN N'اسفند'        THEN N'زمستان'
ELSE NULL
END
SELECT @V1,@V2
GO

۵- بررسی کاربرد Case با استفاده از Having

چنانچه بخواهید شرطی خاص را به یک Aggregation Function اعمال نمایید باید از Having استفاده نمایید. یکی از جالب‌ترین کارهایی که می‌توان با Having انجام داد ترکیب آن با Case می‌باشد در مثال زیر نحوه انجام اینکار بررسی شده است.

SELECT E.GroupCode,E.GroupDesc,MAX(E.Salary) AS Max_Salary FROM Employees E
GROUP BY  E.GroupCode,E.GroupDesc
HAVING
(
MAX
(
CASE WHEN  E.SalaryDesc =N'خوب' OR E.SalaryDesc=N'کم' THEN E.Salary ELSE NULL END
) >=1000
)