فرهود جعفری فرهود جعفری
استفاده از توابع Scalar بجای case
اندازه‌ی قلم متن
تخمین مدت زمان مطالعه‌ی مطلب: یک دقیقه
گاهی از اوقات نیاز است در کوئری‌ها از بین چندین مقدار یکی انتخاب و بجای مقدار اصلی، رشته یا عبارتی جایگزین، نوشته شود. پر استفاده‌ترین راه حل پیشنهادی، استفاده از عبارت case در داخل کوئری هست که بر اساس موارد ممکن، عبارتهای برگشتی نوشته می‌شود. این راه حل خوبی به نظر می‌رسد اما اگر تعداد گزینه‌ها زیاد شود باعث شلوغ شدن متن کوئری و اشکال در بازبینی و نگهداری آن خواهد شد.
یک راه حل دیگر استفاده از توابع نوع Scalar می‌باشد؛ به این صورت که میتوان مقدار استخراج شده از جدول را به تابع تعریف شده فرستاد و در ازاء، مقدار بازگشتی مناسبی را در خروجی مشاهده کرد. حال به یک مثال توجه کنید:
Select Case Gen when 0 then 'مرد' when 1 then 'زن' end As Gen From Table
اکنون استفاده از تابع:
CREATE FUNCTION fcGenName
(
@Gen tinyint
)
RETURNS nvarchar(20)
AS
BEGIN
-- Declare the return variable here
DECLARE @gen nvarchar(20)

-- Add the T-SQL statements to compute the return value here
set @gen = (SELECT case @Gen when 0 then 'مرد' when 1 then 'زن' end as d)

-- Return the result of the function
RETURN @gen

END

و فراخوانی تابع در متن کوئری :

Select fcGenName(Gen) From Table

‫۱۰ سال و ۸ ماه قبل، دوشنبه ۷ بهمن ۱۳۹۲، ساعت ۰۰:۵۵
۲۰۱۸
۵
T-SQLqueryingSQL
مطالب مشابه
  • #
    م رحمانی م رحمانی
    ‫۱۰ سال و ۸ ماه قبل، دوشنبه ۷ بهمن ۱۳۹۲، ساعت ۱۵:۵۲
    سلام و تشکر
    سؤال: این ارجاع به یک تابع تأثیر تو کارایی نداره؟
    • #
      فرهود جعفری فرهود جعفری
      ‫۱۰ سال و ۸ ماه قبل، دوشنبه ۷ بهمن ۱۳۹۲، ساعت ۱۸:۲۳
      با سلام
      ظاهراً در تعداد رکوردهای پایین مشکلی نداره اما در تعداد رکوردهای بالا احتمال کاهش سرعت اجرا دور از ذهن نیست. به هر حال من دقیق تست نکردم اما روی شیوه دیگه هم دارم کار می‌کنم که اون توابع برگشتی از نوع جدول هست که با این شیوه اساساً فرق داره
      • #
        زاهدیان فرد زاهدیان فرد
        ‫۱۰ سال و ۸ ماه قبل، شنبه ۱۹ بهمن ۱۳۹۲، ساعت ۱۵:۱۴
        استفاده از function خوبه مزیتش این که میشه جاهای مختلف استفاده کرد! ولی در تعداد رکورد پایین، چون در رکوردهای زیاد سرعت کوئری به شدت افت میکنه! روش اول بنظرم من بهتر
      • #
        زاهدیان فرد زاهدیان فرد
        ‫۱۰ سال و ۸ ماه قبل، شنبه ۱۹ بهمن ۱۳۹۲، ساعت ۱۵:۴۰
        در SQL server 2012 تابعی اضافه شده به اسم  IIF    که بجای  
        SELECT CASE @GEN WHEN 0 THEN 'Male' ELSE 'Woman' AS Gender
         از این می‌توان استفاده کرد
        SELECT IIF(Gen=0,'Male','Woman')

        • #
          محمد سلیم آبادی محمد سلیم آبادی
          ‫۱۰ سال و ۸ ماه قبل، شنبه ۱۹ بهمن ۱۳۹۲، ساعت ۱۸:۳۳
          در نسخه 2012 جهت سهولت در مهاجرت پایگاه داده‌های Access به SQL Server از توابع CHOOSE و IIF حمایت شده.
           
          منتها تابع IIF چندان انعطاف پذیر نیست. مثلا اگر بخواهید به ازای چند حالت مشخص از داده‌های یک فیلد یک مقدار را برگردانید مجبورید چند تابع IIF تودرتو بنویسید. تودرتو بودن این تابع هم به 10 سطح محدود میشه.
          اما CASE قابلیت‌ها و انعطاف پذیری بیشتری داره.
          سوال میشه گاها کدام یک از این دو Performance یا کارایی بهتر دارد، در جواب میشه گفت هر دو برابر اند. در واقع IIF هنگام اجرا تبدیل به فرم CASE خواهد شد.
          فرض کنید یک نظر سنجی تلوزیونی تنظیم کردیم که مردم از طریق پیامک نظر خودشان را به ما اعلام میکنند. شش گزینه هم داریم. برای انتخاب هر گزینه کافیست از اعداد 1 تا 6 استفاده کنیم. حال هنگام نمایش می‌خواهیم به جای اعداد مقدار متناظر ظاهر شود:

          Use Tempdb
Go

CREATE TABLE [Sample] (value int);
INSERT INTO [Sample] VALUES (1),(2),(3),(4),(5),(6);
Go

--simple CASE Expression
SELECT value,
       CASE Value 
        WHEN 1 THEN 'Very Bad'
        WHEN 2 THEN 'Bad'
        WHEN 3 THEN 'Not Bad'
        WHEN 4 THEN 'Good'
        WHEN 5 THEN 'Very Good'
        WHEN 6 THEN 'Excellent'
       ELSE NULL
END AS [Result]
FROM [Sample];

--CHOOSE Scalar Function
SELECT value,
       CHOOSE(value,'Very Bad','Bad','Not Bad','Good','Very Good','Excellent')
FROM [Sample];


--nested IIF Scalr Function
SELECT value, 
IIF(value = 1, 'Very Bad',
  IIF(value = 2, 'Bad',
    IIF(value = 3, 'Not Bad',
      IIF(value = 4, 'Good',
        IIF(value = 5, 'Very Good', 'Excellent'
           )
         )
       )
     )
   )
FROM [Sample];