مشکل در کار کردن با تابع isnull در t-sql

csharpcollegian · Mar 13, 2016

سلام وقت به خیر
دوستان من برای پیاده سازی یک جستجوی ترکیبی روی یکی از جداول دیتابیسم از Stored Procedure زیر استفاده کردم :

PHP:

CREATE PROCEDURE procPersonnelSearch
    @ID int = Null,
    @Name nvarchar(50) = Null,
    @StartDate datetime = Null,
    @EndDate datetime = Null,
AS
BEGIN

    Select * From tblPersonnel
    Where ID = ISNULL(@ID, ID)
    AND Name = ISNULL(@Name, Name)
    AND StartDate = ISNULL(@StartDate, StartDate)
    And EndDate = ISNULL(@EndDate, EndDate)
END
GO

این Stored Procedure بر اساس متغیرهایی که براش تعریف کردم (مثل نام کارمند، کد پرسنلی کارمند و...) جدولم رو جستجو می کنه و نتیجه مناسب رو برمی گردونه
دلیل اینکه از ISNULL استفاده کردم اینه که بتونم جستجو رو به صورت ترکیبی (چند کلید) هم پیاده سازی کنم
Stored Procedure به خوبی کار می کنه و فقط کافیه متغیر یا متغیرهایی که می خوام بر اساس اونا جستجو انجام بشه رو براش ارسال کنم تا نتیجه رو برگردونه
اما مشکل اینجاست که زمانی که مقدار یکی از پارامتر ها در جدول مربوطش NULL باشه هیچ نتیجه ای رو بر نمیگردونه !
مثلا وقتی مقدار EndDate (تاریخ پایان قرارداد کارمند) در جدول کارمند NULL باشه کد بالا کار نمی کنه !
میخواستم بدونم از چه تکنیک یا تابعی باید استفاده کنم تا در این حالت خاص هم جواب بده ؟
خیلی ممنون

the_king · Mar 13, 2016

csharpcollegian گفت:
سلام وقت به خیر
دوستان من برای پیاده سازی یک جستجوی ترکیبی روی یکی از جداول دیتابیسم از Stored Procedure زیر استفاده کردم :

PHP:

CREATE PROCEDURE procPersonnelSearch @ID int = Null, @Name nvarchar(50) = Null, @StartDate datetime = Null, @EndDate datetime = Null, AS BEGIN Select * From tblPersonnel Where ID = ISNULL(@ID, ID) AND Name = ISNULL(@Name, Name) AND StartDate = ISNULL(@StartDate, StartDate) And EndDate = ISNULL(@EndDate, EndDate) END GO

این Stored Procedure بر اساس متغیرهایی که براش تعریف کردم (مثل نام کارمند، کد پرسنلی کارمند و...) جدولم رو جستجو می کنه و نتیجه مناسب رو برمی گردونه
دلیل اینکه از ISNULL استفاده کردم اینه که بتونم جستجو رو به صورت ترکیبی (چند کلید) هم پیاده سازی کنم
Stored Procedure به خوبی کار می کنه و فقط کافیه متغیر یا متغیرهایی که می خوام بر اساس اونا جستجو انجام بشه رو براش ارسال کنم تا نتیجه رو برگردونه
اما مشکل اینجاست که زمانی که مقدار یکی از پارامتر ها در جدول مربوطش NULL باشه هیچ نتیجه ای رو بر نمیگردونه !
مثلا وقتی مقدار EndDate (تاریخ پایان قرارداد کارمند) در جدول کارمند NULL باشه کد بالا کار نمی کنه !
میخواستم بدونم از چه تکنیک یا تابعی باید استفاده کنم تا در این حالت خاص هم جواب بده ؟
خیلی ممنون

به نظر معقول نمیاد، کوئری رو با شرط های اضافی کند می کنید صرفا به این جهت که در تعداد روتین صرفه جویی کنید.

PHP:

Select * From tblPersonnel
Where ((ID = ISNULL(@ID, ID)) OR (@ID IS NULL AND ID IS NULL))
AND ((Name = ISNULL(@Name, Name)) OR (@Name IS NULL AND Name IS NULL))
AND ((StartDate = ISNULL(@StartDate, StartDate)) OR (@StartDate IS NULL AND StartDate IS NULL))
AND ((EndDate = ISNULL(@EndDate, EndDate)) OR (@EndDate IS NULL AND EndDate IS NULL))

csharpcollegian · Mar 13, 2016

the_king گفت:
به نظر معقول نمیاد، کوئری رو با شرط های اضافی کند می کنید صرفا به این جهت که در تعداد روتین صرفه جویی کنید.

PHP:

Select * From tblPersonnel Where ((ID = ISNULL(@ID, ID)) OR (@ID IS NULL AND ID IS NULL)) AND ((Name = ISNULL(@Name, Name)) OR (@Name IS NULL AND Name IS NULL)) AND ((StartDate = ISNULL(@StartDate, StartDate)) OR (@StartDate IS NULL AND StartDate IS NULL)) AND ((EndDate = ISNULL(@EndDate, EndDate)) OR (@EndDate IS NULL AND EndDate IS NULL))

واقعا ازتون ممنونم جناب the_king ، همیشه پاسخ سوال هایی که هیچ جا به جوابشون نرسیدم رو از شما میگیرم

******************

اما در رابطه با این که گفتید این تکنیک معقول نیست، میخواستم نظر شما رو بدونم یا بهتر بگم خیلی برام مهمه که نظر شما رو بدونم
در حالت جستجوی ساده (تک کلید) کلا سه حالت میشد کد زد (البته در حد توانایی من) :

حالت اول :
در این حالت برای هر کلید جستجو یک Stored Procedure مجزا نوشته سپس در برنامه با استفاده از دستور if کلید جستجو رو مشخص کرده و Stored Procedure مربوط به اون کلید را صدا میزنیم :

C# Code

PHP:

public DataSet Search()
{
    DataSet ds = new DataSet();
    if (ID != null)
    {
        SqlParameter idParam = new SqlParameter("@ID", ID);
        DAL.ExecuteProcedure(ds, "procSearchByID", idParam);
    }
    else if(Name != null)
    {
        SqlParameter nameParam = new SqlParameter("@Name", Name);
        DAL.ExecuteProcedure(ds, "procSearchByName", nameParam);
    }
    .
    .
    .
    return ds;
}

Stored Procedure

PHP:

CREATE PROCEDURE procSearchByID
    @ID int
AS
BEGIN
    Select * From tblPersonnel Where ID = @ID
END

در این حالت هم تعداد Stored Procedure ها خیلی زیاد میشه(به ازای هر کلید یک Stored Procedure) هم کد برنامه خیلی شلوغ میشه و if های متعددش سرعت رو میاره پایین

حالت دوم :
در این حالت در کد برنامه هیچ چیزی رو چک نمی کنیم، فقط تمامی پارامترها رو ساخته و به Stored Procedure میفرستیم و خود Stored Procedure با بررسی پارامترهای ارسال شده کلید جستجو رو پیدا کرده و نتیجه رو برمی گردونه :

C# Code

PHP:

public DataSet Search()
{
    DataSet ds = new DataSet();
    SqlParameter idParam = new SqlParameter("@ID", this.ID);
    SqlParameter nameParam = new SqlParameter("@Name", this.Name);
    .
    .
    .
    DAL.ExecuteProcedure(ds, "procSearch", idParam, nameParam, ...);
     return ds;
}

Stored Procedure

PHP:

CREATE PROCEDURE procSearch
    @ID int = Null,
    @Name nvarchar(50) = Null,
    .
    .
    .
AS
BEGIN
    if(@ID IS Not Null)
        Select * From tblPersonnel Where ID = @ID
    else if(@Name IS Not Null)
        Select * From tblPersonnel Where Name = @Name
    else if
    .
    .
    .
END

حالت سوم :
این همون حالتیه که من ازش استفاده کردم، کد سی شارپ این حالت دقیقا مثل حالت دوم هستش :

Stored Procedure

PHP:

CREATE PROCEDURE procSearch
    @ID int = Null,
    @Name nvarchar(50) = Null,
    .
    .
    .
AS
BEGIN
    Select * From tblPersonnel Where ((ID = ISNULL(@ID, ID)) OR (ID IS Null And @ID IS Null))
    And ((Name = ISNULL(@Name, Name)) OR (Name IS Null And @Name IS Null))
    .
    .
    .
END

خب اگر بخواهیم مقایسه کنیم حالت دوم از حالت سوم بهتره، چون کد Stored Procedure اون خیلی ساده تر و خواناتره اما دلیل اینکه من از حالت سوم استفاده کردم اینه که این حالت، جستجوی ترکیبی(چند کلید) رو هم ساپورت می کنه
اگر بخواهیم جستجوی ترکیبی(چند کلید) رو برای حالت اول و سوم پیاده سازی کنیم، کافیه شش پارامتر داشته باشیم تا تعداد if ها سر به فلک بکشه !!! چون ممکنه هر پارامتر با یک پارامتر یا دو پارامتر یا n پارامتر دیگه به صورت ترکیبی به عنوان کلید جستجو استفاده بشه که چون کاربر این رو در زمان اجرای برنامه مشخص می کنه، باید تمام حالت ها با if چک بشن !
مثلا فرض کنید کاربر میخواد توو یک بازه زمانی (StartDate And EndDate) کارمندهای قراردادی ای (Type) که میزان حقوقشون (Salary) از فلان قدر بالاتره و در بخش کنترل کیفیت (Part) کار میکنند رو جستجو کنه یا بخش کاری کارمندها رو از ملاک های جستجو حذف کنه یا و یا و... که تعداد if ها رو برای تشکیل کلید جستجو بسیار بالا میبره و استفاده از حالت اول و دوم رو غیر ممکن می کنه !
حالا میخواستم بپرسم که آیا با این تفاسیر، روشی که انتخاب کردم بهینه نیست ؟ آیا شما الگوریتم بهتری در نظر دارید ؟
ببخشید اگر سوالم خیلی طولانی شد
خیلی ممنون

the_king · Mar 13, 2016

پیشنهاد می کنم که از همون Stored Procedure بخواهید که پارامتر های ارسالی رو بررسی کنه و بر اساس مقدارشون Query رو بنویسه و بعد اجرا کنه.
خود روتین یکی یکی پارامتر ها رو بررسی می کنه و مواردی که شما در نظر دارید رو به متن Query اضافه می کنه و نهایتا اجراش می کنه. دیگه نیازی نیست که نگران null بودن مقدار فیلد ها
در جدول باشید، از اونجایی که این Query بصورت پویا طراحی میشه اگر پارامتری تعیین نشه (null باشه) در Query اصلا مطرح نمیشه و همیشه یک Query مختصر و مفید می سازه :

PHP:

CREATE PROCEDURE procPersonnelSearch
    @ID int = Null,
    @Name nvarchar(50) = Null,
    @StartDate datetime = Null,
    @EndDate datetime = Null
AS
BEGIN
	SET NoCount ON
	DECLARE @QUERY varchar(1000)
	SELECT @QUERY = ''
	IF (@ID IS NOT NULL)
		BEGIN
			IF (@QUERY = '')
				SELECT @QUERY = @QUERY + ' WHERE'
			ELSE
				SELECT @QUERY = @QUERY + ' AND';				
			SELECT @QUERY = @QUERY + ' ID = @ID';
		END
	IF (@Name IS NOT NULL)
		BEGIN
			IF (@QUERY = '')
				SELECT @QUERY = @QUERY + ' WHERE'
			ELSE
				SELECT @QUERY = @QUERY + ' AND';				
			SELECT @QUERY = @QUERY + ' Name = @Name';
		END
	IF (@StartDate IS NOT NULL)
		BEGIN
			IF (@QUERY = '')
				SELECT @QUERY = @QUERY + ' WHERE'
			ELSE
				SELECT @QUERY = @QUERY + ' AND';				
			SELECT @QUERY = @QUERY + ' @StartDate = StartDate';
		END
	SELECT @QUERY = 'Select * From tblPersonnel' + @QUERY
	EXEC (@QUERY)
	SET NoCount OFF
END
GO

csharpcollegian · Mar 14, 2016

the_king گفت:
پیشنهاد می کنم که از همون Stored Procedure بخواهید که پارامتر های ارسالی رو بررسی کنه و بر اساس مقدارشون Query رو بنویسه و بعد اجرا کنه.
خود روتین یکی یکی پارامتر ها رو بررسی می کنه و مواردی که شما در نظر دارید رو به متن Query اضافه می کنه و نهایتا اجراش می کنه. دیگه نیازی نیست که نگران null بودن مقدار فیلد ها
در جدول باشید، از اونجایی که این Query بصورت پویا طراحی میشه اگر پارامتری تعیین نشه (null باشه) در Query اصلا مطرح نمیشه و همیشه یک Query مختصر و مفید می سازه :

PHP:

CREATE PROCEDURE procPersonnelSearch @ID int = Null, @Name nvarchar(50) = Null, @StartDate datetime = Null, @EndDate datetime = Null AS BEGIN SET NoCount ON DECLARE @QUERY varchar(1000) SELECT @QUERY = '' IF (@ID IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' ID = @ID'; END IF (@Name IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' Name = @Name'; END IF (@StartDate IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' @StartDate = StartDate'; END SELECT @QUERY = 'Select * From tblPersonnel' + @QUERY EXEC (@QUERY) SET NoCount OFF END GO

الگوریتم منعطف و بهینه ایه خیلی ممنونم
در ضمن مجددا متشکرم که حوصله به خرج دادین و پست طولانیم رو بررسی کردین

csharpcollegian · Mar 27, 2016

the_king گفت:
پیشنهاد می کنم که از همون Stored Procedure بخواهید که پارامتر های ارسالی رو بررسی کنه و بر اساس مقدارشون Query رو بنویسه و بعد اجرا کنه.
خود روتین یکی یکی پارامتر ها رو بررسی می کنه و مواردی که شما در نظر دارید رو به متن Query اضافه می کنه و نهایتا اجراش می کنه. دیگه نیازی نیست که نگران null بودن مقدار فیلد ها
در جدول باشید، از اونجایی که این Query بصورت پویا طراحی میشه اگر پارامتری تعیین نشه (null باشه) در Query اصلا مطرح نمیشه و همیشه یک Query مختصر و مفید می سازه :

PHP:

CREATE PROCEDURE procPersonnelSearch @ID int = Null, @Name nvarchar(50) = Null, @StartDate datetime = Null, @EndDate datetime = Null AS BEGIN SET NoCount ON DECLARE @QUERY varchar(1000) SELECT @QUERY = '' IF (@ID IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' ID = @ID'; END IF (@Name IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' Name = @Name'; END IF (@StartDate IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' @StartDate = StartDate'; END SELECT @QUERY = 'Select * From tblPersonnel' + @QUERY EXEC (@QUERY) SET NoCount OFF END GO

عرض سلام مجدد
جناب the_king وقتی از کد شما استفاده می کنم، در حالتی که هیچ پارامتری رو مقدار نمیدم، رویه درست عمل می کنه و کل رکوردها رو برمی گردونه
ولی وقتی برای یک پارامتر، مقداری ارسال میکنم خطا میده ! مثلا وقتی برای پارامتر ID@ مقداری میفرستم این خطا رو میگیره :

PHP:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ID"

برای هر پارامتری مقدار بفرستم مشابه همین خطا رو واسه اون پارامتر میگیره...
مشکل از کجاست ؟

the_king · Mar 29, 2016

csharpcollegian گفت:
عرض سلام مجدد
جناب the_king وقتی از کد شما استفاده می کنم، در حالتی که هیچ پارامتری رو مقدار نمیدم، رویه درست عمل می کنه و کل رکوردها رو برمی گردونه
ولی وقتی برای یک پارامتر، مقداری ارسال میکنم خطا میده ! مثلا وقتی برای پارامتر id@ مقداری میفرستم این خطا رو میگیره :

PHP:

msg 137, level 15, state 2, line 1 must declare the scalar variable "@id"

برای هر پارامتری مقدار بفرستم مشابه همین خطا رو واسه اون پارامتر میگیره...
مشکل از کجاست ؟

اشتباه از من بود، exec در محیطی مستقل اجرا میشه به همین جهت پارامتر هایی مثل id@ رو نمی بینه، باید موقع ساختن Query به مقدار رشته ای تبدیل شون می کردم که از قلم افتاده بود.
برای متغیر های غیر رشته ای با CONVERT اونها رو به یک رشته varchar با حداکثر طول مورد نظر تبدیل می کنیم.
برای متغیر های رشته ای با CAST مقدار داخل اونها رو در رشته قرار می دهیم.

کد:

	IF (@ID IS NOT NULL)
		BEGIN
			IF (@QUERY = '')
				SELECT @QUERY = @QUERY + ' WHERE'
			ELSE
				SELECT @QUERY = @QUERY + ' AND';				
			SELECT @QUERY = @QUERY + ' ID = ' + [B][COLOR="#0000FF"]CONVERT(varchar(9),@ID)[/COLOR][/B];
		END
	IF (@Name IS NOT NULL)
		BEGIN
			IF (@QUERY = '')
				SELECT @QUERY = @QUERY + ' WHERE'
			ELSE
				SELECT @QUERY = @QUERY + ' AND';				
			SELECT @QUERY = @QUERY + ' Name = "' + [B][COLOR="#0000FF"]CAST(@Name AS varchar)[/COLOR][/B] + '"';
		END
	IF (@StartDate IS NOT NULL)
		BEGIN
			IF (@QUERY = '')
				SELECT @QUERY = @QUERY + ' WHERE'
			ELSE
				SELECT @QUERY = @QUERY + ' AND';				
			SELECT @QUERY = @QUERY + ' StartDate = "' + [B][COLOR="#0000FF"]CONVERT(varchar(19),@StartDate)[/COLOR][/B] + '"';
		END

csharpcollegian · Mar 30, 2016

the_king گفت:
اشتباه از من بود، exec در محیطی مستقل اجرا میشه به همین جهت پارامتر هایی مثل id@ رو نمی بینه، باید موقع ساختن Query به مقدار رشته ای تبدیل شون می کردم که از قلم افتاده بود.
برای متغیر های غیر رشته ای با CONVERT اونها رو به یک رشته varchar با حداکثر طول مورد نظر تبدیل می کنیم.
برای متغیر های رشته ای با CAST مقدار داخل اونها رو در رشته قرار می دهیم.

کد:

IF (@ID IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' ID = ' + [B][COLOR=#0000FF]CONVERT(varchar(9),@ID)[/COLOR][/B]; END IF (@Name IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' Name = "' + [B][COLOR=#0000FF]CAST(@Name AS varchar)[/COLOR][/B] + '"'; END IF (@StartDate IS NOT NULL) BEGIN IF (@QUERY = '') SELECT @QUERY = @QUERY + ' WHERE' ELSE SELECT @QUERY = @QUERY + ' AND'; SELECT @QUERY = @QUERY + ' StartDate = "' + [B][COLOR=#0000FF]CONVERT(varchar(19),@StartDate)[/COLOR][/B] + '"'; END

جناب the-King واقعا ممنونم ازتون، این روش جواب داد
فقط جسارتا یه سوالی داشتم از خدمتتون...حالا که پارامترها رو تبدیل به رشته می کنیم و محتویات داخلشون رو مستقیما داخل کوئری قرار میدیم امنیت برنامه در مقابل SQL Injection پایین نمیاد ؟
فکر می کنم اینجوری برنامه در مقابل SQL Injection ضعیف میشه و راحت میشه به دیتابیس صدمه زد، مثل حالتی که برنامه نویس بدون استفاده از پارامترها، کوئری رو با قرار دادن مستقیم داده ها از فرم ایجاد میکنه، درسته ؟
ببخشید که انقدر زیاد سوال پرسیدم ازتون...
خیلی حیلی ممنونم

the_king · Mar 30, 2016

csharpcollegian گفت:
جناب the-King واقعا ممنونم ازتون، این روش جواب داد
فقط جسارتا یه سوالی داشتم از خدمتتون...حالا که پارامترها رو تبدیل به رشته می کنیم و محتویات داخلشون رو مستقیما داخل کوئری قرار میدیم امنیت برنامه در مقابل SQL Injection پایین نمیاد ؟
فکر می کنم اینجوری برنامه در مقابل SQL Injection ضعیف میشه و راحت میشه به دیتابیس صدمه زد، مثل حالتی که برنامه نویس بدون استفاده از پارامترها، کوئری رو با قرار دادن مستقیم داده ها از فرم ایجاد میکنه، درسته ؟
ببخشید که انقدر زیاد سوال پرسیدم ازتون...
خیلی حیلی ممنونم

ممکنه، اما بستگی به میزان بررسی ای داره که قبل از ارسال پارامتر ها انجام می دهید.
اگر پارامتر ای که ارسال می کنید، مثلا Name@ قبل از ارسال بررسی بشه امکان SQL Injection نیست. مثلا با حذف کردن و یا تغییر دادن کاراکتر های ' و " یا Escape کردن رشته