استفاده از توابع Scaller به جای case

[nathan]

برای همه ما پیش میاد که در کوئری هامون باید از بین چند مقدار یکی انتخاب و به جای مقدار اصلی رشته یا عبارتی جایگزین نوشته بشه. پر استفاده‌ترین راه حل پیشنهادی استفاده از عبارت case در داخل کوئری هست که بر اساس موارد ممکن عبارتهای برگشتی نوشته می‌شود. این راه حل خوبی به نظر می‌رسد اما اگر تعداد گزینه‌ها زیاد شود باعث شلوغ شدن متن کوئری و اشکال در بازبینی و نگهداری آن میشود.
یک راه حل دیگر استفاده از توابع نوع Scaller می‌باشد، به این صورت که میتوان مقدار استخراج شده از جدول را به تابع تعریف شده فرستاد و در ازا مقدار بازگشتی مناسب را در خروجی مشاهده کرد. حال به یک مثال توجه کنید:



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

[starting]

IIF(col=1,'Male','Female')

[nathan]

IIF(col=1,'Male','Female')

حالا اگر مجبور به انتخاب حداقل 15 تا 20 آیتم باشید آیا بازهم این روش جوابگو هست؟

[starting]

سلام
از لحاظ syntactically بله امکان پذیر است. اما مجبوریم تعداد زیادی تابع تودرتو بنویسیم. که حداقل برای توسعه، خوانایی و نگهداری از اون، این روش عاقلانه بنظر نمیرسه. از لحاظ تئوری اگر اشتباه حساب نکرده باشم برای 20 وضعیت نیاز به 19 تابع nested IIF داریم.

برای این منظور همون CASE مناسب هست. که به گفته شما میشه این CASE که چند وضعیتی هست را داخل یک تابع تعریف شده توسط کاربر (Scalar UDF) قرار داد و از مزیت های اون که شما به خوبی به اون اشاره کردین بهره مند شد. اما از تاثیر نامطلوب اون بر روی کارایی مطمئن نیستم.

[nathan]

سلام
برای این منظور همون CASE مناسب هست. که به گفته شما میشه این CASE که چند وضعیتی هست را داخل یک تابع تعریف شده توسط کاربر (Scalar UDF) قرار داد و از مزیت های اون که شما به خوبی به اون اشاره کردین بهره مند شد. اما از تاثیر نامطلوب اون بر روی کارایی مطمئن نیستم.

حالا اگر می شد یک راه حلی پیدا می کردیم که به جای تابع می شد در جایی چیزی شبیه به دیکشنری که در C# استفاده میشه رو به وجود می آوردیم خیلی جالبتر میشد، چون حتی در تابع هم مجبور به استفاده از Case هستیم اما خودم به شخصه دنبال راهی هستم که از جدول استفاده نشه از case هم استفاده نشه و از قابلیتهای جدید خود Sql Server یا Sql بشه استفاده کرد مثل Assemblies , Types و از این قبیل. . .

[حمیدرضاصادقیان]

سلام.
دستور IIF و Choose در SQL Server 2012 برای پشتیبانی از Access اضافه شده و جز زبان SQL Ansi نیست. برای بحث تودرتو بودن هم IIF هم Case تا 10 مرحله رو بیشتر به صورت تودرتو پشتیبانی نمی کنند و احتمالا باید از روشهای دیگه ای استفاده کرد.مناسبترین راه استفاده از Case هست. توابع Scalar تاثیر منفی روی رکوردهای زیاد بر روی خروجی شما خواهند داشت .چون به ازای هررکورد صدا زده می شوند و مقدار برگشت داده شده و ... همین روند تا به انتهای رکوردها ادامه دارد.همچنین اگر تعداد انتخاب های شما زیاده احتمال اینکه طراحی جداول هم مشکل دار باشه زیاده که فکر میکنم با یک تغییر جزئی در طراحی بشه از این شرایط خلاص شد.

[nathan]

سلام.
مناسبترین راه استفاده از Case هست. توابع Scalar تاثیر منفی روی رکوردهای زیاد بر روی خروجی شما خواهند داشت .چون به ازای هررکورد صدا زده می شوند و مقدار برگشت داده شده و ... همین روند تا به انتهای رکوردها ادامه دارد.همچنین اگر تعداد انتخاب های شما زیاده احتمال اینکه طراحی جداول هم مشکل دار باشه زیاده که فکر میکنم با یک تغییر جزئی در طراحی بشه از این شرایط خلاص شد.

اگه به جای Case از یک جدول Static واسه نگهداری اطلاعات ضمیمه ای استفاده کنیم و از join استفاده کنیم چی؟ فکر می کنم بهترین حالت همین استفاده از جدول استاتیک و Join هستش. البته تصحیح کنم در مواردی که قراره تعداد Case ها خیلی زیاد بشن. البته اگر آقای صادقیان لطف کنن یکم رو این دو خط آخر توضیح بدن که دیگه عالی میشه!

[حمیدرضاصادقیان]

عرض کردم بستگی به نوع داده ها داره. بله این موردی که گفتین خیلی بهتره و کاراتره و خیلی کارهای مختلف نیز میشه روش انجام داد از قبیل ایندکس گذاری و Tune کردن Query ها برای افزایش سرعت کار.ولی این نیازمند این هست که دقیق بدونیم نوع داده ها چی هست و دلیل این همه شرط چیست؟؟خیلی وقتها مقادیر Dynamic هستند و در یک جدول هستند که با تجزیه به چند فیلد و جدول جداگانه هم حالت نرمال تری پیدا میکنه هم کوچک شدن جدول در Query های شما نتیجه بهتری از لحاظ سرعتی خواهد داد.

[starting]

با تشکر از آقای مدیر،
بهتره در مورد گفته قبلیم یه تجدید نظر داشته باشم. توابع IIF را حداکثر در 10 سطح می توان تودرتو نوشت. که دلیل رفتن به سراغ این تودرتو بودن نیز محدودیت و عدم انعطاف پذیری خود تابع IIF بود. اما در مورد CASE من مورد منطقی نمی شناستم که نیاز به نوشتن CASE به فرم تودرتو به این شکل باشد. پس محدودیت تعداد سطوح تودرتو بودن خللی به CASE وارد نمیکنه.