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

دستور Case در  SQL Server چیست ؟

دستور Case در  SQL Server ، یک عبارت شرطی (Conditional Expression) منعطف و قدرتمند است که به کمک آن می‌توانید منطق شرطی را درون کوئری‌های (Queries | پرس و جو) خود استفاده کنید. معمولاً از دستور Case، برای ساختن عبارت‌های شرطی، بدست آوردن ستون‌های حاصل از  اجرای عبارات SELECT، قسمت کلازهای (clauses) مربوط به WHERE و سایر بخش‌های کوئری های SQL استفاده می‌شود. در این مطلب، قصد داریم همه آنچه برای آشنایی با دستور Case در  SQL Server نیاز دارید را شرح دهیم.

کار با دستور Case در  SQL Server

اکنون انواع روش‌هایی را معرفی می‌کنیم که به واسطه آن‌ها، می‌توانید دستور Case در SQL Server را استفاده کنید.

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

می‌خواهیم شکل ساده و کلی دستور Case در SQL Server را به همراه مثال مورد بررسی قرار دهیم. 

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

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

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

معنای بخش‌های مختلف این دستور به صورت زیر است:

• CASE: کلمه کلیدی برای شروع عبارت CASE در SQL Server
• WHEN: کلمه WHEN ، برای قید کردن شرط‌ها و نتایج مرتبط با هر یک از آن‌ها
• ELSE: این بخش اختیاری است، در صورتی که شرط‌های قبلی قابل اجرا نباشند، می‌توان بدین طریق یک نتیجه پیش‌فرض دریافت کرد.
• END: پایان عبارت شرطی CASE در SQL Server

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

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 در SQL Server ، عبارت مورد نظرتان  با یک یا چند عبارت از نوع 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 را توسط انواع دستورات SQL مورد استفاده قرار داد. در ادامه مقاله، نمونه‌ای از کاربرد Case به همراه دستورات SQL را بررسی خواهیم کرد.
برای اینکه بتوانیم مثال‌ها را به طور شفاف و قابل درک بررسی کنیم، جدول Employees را در بانک اطلاعاتی Tempdb ایجاد می‌کنیم. لطفا توجه کنید که بانک اطلاعاتی 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 به همراه انواع دستورات SQL

در این بخش از مقاله بررسی دستور Case از SQL Server ، به نحوه استفاده آن با سایر دستورات SQL می‌پردازیم.

۱- بررسی کاربرد 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 در SQL Server ، به صورت 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 در SQL Server استفاده کرد تا عمل به روز رسانی در پایگاه داده انجام شود.

هنگام استفاده از دستور 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، نوع 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 در SQL Server به صورت زیر استفاده نمود.
در صورتی که هنگام مرتب‌سازی رکوردها، نوع 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 در SQL Server امکان پذیر می‌باشد. به قطعه کد زیر توجه کنید.

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 در SQL Server می‌باشد. در مثال زیر نحوه انجام این کار بررسی شده می‌شود.

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

جمع‌بندی دستور Case در SQL Server

دستور Case در SQL Server ، یک روش کاربردی برای پیاده‌سازی منطق شرطی درون کوئری های SQL محسوب می‌شود، فارق از اینکه هدفتان بدست آوردن ستون‌های حاصل از یک دستور SELECt باشد یا اجرای توابع تجمعی شرطی، دستور Case در SQL Server می‌تواند کوئری های شما را به پرسمان‌های منعطف‌تر و پویاتری تبدیل کند.