فرض کنید میخواهید یک stored procedure بنویسد که عمل جستجو در یک تیبل انجام دهد
مثلا تیبل اطلاعات شخصی با فیلدهایی مانند:
username, firstName, LastName, fatherName, gender, ...
را در نظر بگیرید.اگر
بخواهیم این پروسیجر را طوری بنویسیم که پر کردن هر سه پارامتر آن الزامی نباشد،
احتمالا stored procedure شما، چیزی شبیه
این زیر خواهد شد:
1 create procedure search(
2 @firstName nvarchar(100)='',
3 @lastName nvarchar(100)='',
4 @fatherName nvarchar(100)=''
5 ) as
6 declare @Query nvarchar(500)
7 set @query = ''
8 if @firstName <> ''
9 set @query = @query + ' and firstName = N''' + @firstName + ''''
10 if @lastName <> ''
11 set @query = @query + ' and lastName = N''' + @lastName + ''''
12 if @fatherName <> ''
13 set @query = @query + ' and fatherName = N''' + @fatherName + ''''
14 if @query <> ''
15 begin
16 set @query = right(@query, len(@query) - 4 )
17 set @query = 'select * from Users where ' + @query
18 end
19 exec ( @Query )
دستور SQL بصورت پویا ساخته شده و درون یک متغیر از نوع کارکتری ذخیره میشود تا در نهایت با استفاده از دستور EXEC اجرا
شود. در خطهای 8، 10 و 12 بررسی میکنیم که اگر پارامتر خالی باشد، شرطی به ازای آن اضافه نکنیم.در خط 16 با برداشتن یک از اول جمله
شرطی، آن را تصحیح و در نهایت جمله SQL را میسازیم(خط 17) مزیت روش بالا این است که دست برنامه نویس برای انجام کارهای گوناگون باز است ولی
در ضمن اشکالاتی نیز بر این روش مترتب است:
1. بدلیل اینکه دستور SQL بصورت پویا ساخته شده است، امکان کامپایل شدن آن وجود ندارد بنابراین سرعت اجرای این پروسیجر پایین میاید
2.باز بدلیل اینکه دستور SQL بصورت پویا ساخته شده است،امکان حملات SQL INJECTION وجود دارد.
3. پیچیدگی کد زیاد میشود. مثلا خطهای 9، 11 و13 را ببینید که برای ساختن جمله شرطی، از چند single quotation
پشت سرهم استفاده شده است. و همچنین تعداد خطوط پروسیجر به 19 خط رسیده است.
بدلیل وجود این ضعفها، روش دیگری را میتوان استفاده کرد که کد زیر مبین آن
است:
create procedure Search(
@firstName nvarchar(100)='',
@lastName nvarchar(100)='',
@fatherName nvarchar(100)=''
) as
select * from Users where
(@firstName = '' or firstName like @firstName )
and
(@lastName = '' or lastName like @lastName )
and
(@fatherName = '' or fatherName like @fatherName )
همانطور که میبینید کد، بسیار خلاصه تر و خواناتر گردیده است علاوه بر اینکه اشکالات روش قبلی را نیز ندارد. مبنای اصلی این روش
استفاده بهینه از اپراتور or است که در صورتی که هر کدام از پارامترها خالی باشد، شرط بعد از خود را بی اثر میکند.
راه حل ارائه شده در بالا، اکثرا در مواردی استفاده میشود که قرار است بصورت کلی جستجویی بر روی یک تیبل انجام شود. ولی مطمئنا با استفاده از این
نمونه کد میتوانید بسیاری از پروسیجرهای خود را بهبود بخشید. |