مشکل در جستجوی رکوردها

[حسین محمدی]

در یک جدول که داراری 3 فیلد است رکوردهایی وارد شده است که در برخی از آنها مقدار برخی فیلدها به صورت NULL وارد شده است. یک فبلد عددی هم primary key در جدول است.
در برنامه خود برای جستجو در این table و یافتن رکوردهای مورد نظر از 3 تکست باکس استفاده می کنم که کاربر به منظور search در آنها مقدار می نویسد ولی در جاهایی که کاربر مقادیر تکست باکس را خالی می گذارد تا در جدول رکوردهایی را که به ازای آن text box مقادیر NULL را جستجو کند با مشکل مواجه می شوم.
می خواستم ببینم چگونه مقادیر NULL را در جدول جستجو کنم؟
آیا صحیح است که بنویسم:

'' = Select * from myTable where field1 = 'x' and field2 = 'y' and filed3

یا برای جستجو در رکوردهایی که برخی از فیلدهای آن NULL هستند باید دستور دیگری بنویسم؟

[rezaei manesh]

Select * from myTable where field1 = 'x' and field2 = 'y' and filed3 is null

موفق باشی

[حسین محمدی]

با تشکر
ولی مشکل اینجاست که در جستجو از ابتدا مشخص نسیت که کدام فیلد NULL است.
مثلا ممکن است یکبار کاربر رکوردهایی را جستجو کندکه در آنها فیلد سوم NULL باشد و گاهی کاربر مواردی را جستجو می کند که در آن فیلد دوم NULL و گاهی فیلد دوم و سوم هردو با هم. آیا باید دستور جستجو را در هر بار متناسب با مقدار فیلدها بسازم؟
چنانچه این جستجو توسط یک stored procedure انجام شود چگونه باید دستور search را بنویسم؟
در ضمن یک سوال داشتم می توان در sql server 2000 خروجی یک جستجو به صورت یک table‌به برنامه خود ارجاع داد؟

[rezaei manesh]

بله باید در دستور جستجو چک کنی که اگه مقداری برابر با nothing بود از مقدار null به جای اون استفاده کنی البته دقت کن اگه یکی از فیلد های جدولت مقدار nothing داشته باشه نه null این دستور اون رو پیدا نمی کنه مقگر اینکه یک or به دستورت اضافه کنی و nothing رو هم چک کنی و..
بله می تونی نتیجه جستجو رو به صورت جدول داشته باشی -یک سلکت از فیلد هایی که می خواهی ببینی و با شرطی که از جستجوی کاربر می گیری.

[حسین محمدی]

با تشکر از راهنمایی شما
می توانید برای من یک خط کد نیز بنویسید؟
در مورد nothing نیز اگر توضیح دهید ممنون می شوم؟ فرق آن با NULL چسیت؟
در مورد دوم نیز بیشتر توضیح می دهید؟ در برنامه خود چگونه به آن table دسترسی داشته باشم؟

[rezaei manesh]

Dim temp, query As String
'temp=txtser.text.trim
query = "select fld1,fld2 + '-' + fld3 from yourTableName "
If temp = Nothing Then 'nothing is ""
query = " where fld1 is 'null' or fld1 = ''"
Else
query = " where fld1= '" & temp & "' "
End If

null زمانی هست که فیلد مقدار دهی نشده است به عبارتی هیچی در آن نیست اگه از تو بانک نگاه کنی مقدار اون فیلد ها رو null می بینی اما nothing مقدار پیش فرض یک متغیر رو قرار می ده که برای رشته "" هست به عبارتی یعنی هیچی اگه تو سایت جستجو کنی دوستان کامل توضیح دادند و...
در رابطه با سوال سوم مثل بقیه جداول رفتار کن

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

البته اگر میتونی برای فیلدهات یک مقدار پیش فرض تعیین کن و هیچ وقت مقدار null در دیتابیست ذخیره نکن.



The use of NULLs in a
database can greatly reduce database performance, especially in WHERE
clauses.

Here are the key operands used in the WHERE clause, ordered by their
performance. Those operands at the top will produce results faster than those
listed at the bottom.
A single literal used by itself on one side of an operator.
A single column name used by itself on one side of an operator, a single
parameter used by itself on one side of an operator.
A multi-operand expression on one side of an operator.
A single exact number on one side of an operator.
Other numeric number (other than exact), date, and time.
Character data, NULLs.
The simpler the operand, and using exact numbers, provides the best overall



منبع :
http://www.sql-server-performance.com

[AminSobati]

حسین جان زمانی که کاربر Textbox مربوط به یک فیلد رو پر نمیکنه، شما به چه صورت تشخیص میدین که منظورش SomeColumn IS NULL هستش یا اینکه اصلا Search روی اون فیلد انجام نمیده؟

[rezaei manesh]

سلام
حمید رضا جان دستت درد نکنه تذکر جالبی دادی من تا به حال بر عکس فکر می کردم !
در ضمن با آقای ثباتی هم عقیده هستم (از کجا می فهمی که نمی خواد روش سرچ کنه؟)

[AminSobati]

ضمنا دیدن از مثال برای ساختن Dynamic Search شاید کمکتون کنه:


create proc sp1
@country varchar(20)=null,
@city varchar(20)=null
as

declare @sql nvarchar(max)
DECLARE @Param nvarchar(500)
set @sql='select * from customers where 1=1'
set @param=N'@xcity varchar(20), @xcountry varchar(20)'

if @country is not null
set @sql=@sql+' and country=@xcountry'

if @city is not null
set @sql=@sql+' and city=@xcity'

print @sql
exec sp_executesql @sql,@param,@xcity=@city,@xcountry=@country
go


exec sp1 @country='uk',@city='london'
exec sp1 @city='spain'
exec sp1 @country='uk'
exec sp1

[rezaei manesh]

جناب ثباتی با اجازتون من می خوام 2 تا سوال در مورد این کد شما بپرسم جواب اولی رو فکر کنم بدونم اما برای اطمینان می پرسم
دستور where 1=1 رو آیا برای این گذاشتید که هر بار چک نکنید که شرطی وجود داره یا نه که and یا where بزارید ؟ خواستید where همیشه بمونه و شما همیشه قبل هر شرط بتونید and بیارید؟ اگه آره ایا بهتره از این روش استفاده کنیم یا شرط چک کنیم یا مثلا یه متغیر کمکی بگیریم و...
در مورد مقدار دهی param@ و استفاده از اون اگه می شه یه کوچولو توصیح بدید
چرا از متغیر های مثل xcity استفاده کردید؟
من اگه بودم کد بالا به این شکل می نوشتم


create proc sp1
@country varchar(20)=null,
@city varchar(20)=null
as
declare @sql nvarchar(max)

set @sql='select * from customers where 1=1'

if @country is not null
set @sql=@sql+' and country= '+ @country

if @city is not null
set @sql=@sql+' and city= '+@city

exec sp_executesql @sql
go

خوب حالا می خوام فرق بین یه شاگرد و استاد رو به من بگید

[AminSobati]

داستان 1=1 دقیقا همونه که اشاره کردین، کد نویسی رو راحت میکنه اما موقع اجرای Query، عبارت 1=1 توسط Optimizer حذف میشه.
اگر sp_executesql رو به شکلی که شما نوشتین اجرا کنیم، عملا فرقی بین این SP و استفاده از EXEC خالی نیست، یعنی این کار رو هم میشه کرد:


exec (@sql)

اما اصل موضوع اینه که exec همیشه Query رو از اول Compile میکنه چون باید Stringی که شما ساختین، اصطلاحا Parameterized بشه. اما در sp_executesql شما خودتون Parameterized میکنین و این کمک بزرگیه به Optimizer!
وقتی مینویسین and country=@xcountry با این کار خودتون مشخص میکنین که فیلد Country با یک پارامتر باید مقدارش چک بشه و مشخصات این پارامتر رو در Param@ ارائه میکنید، مقدارش هم در ادامه دستور sp_executesql.
حالا فرض کنین یک Query دارین که 5 فیلدش باید قابل جستجو باشه. پس پنج Textbox به کاربر میدین. اگر کاربر Textbox اول و چهارم رو پر کنه، Query شما با این دو فیلد ساخته و Parameterized میشه و Compile هم صورت میگیره. اما فقط یکبار! یعنی اگر کاربر دیگه ای در اون فرم، Textbox اول و چهارم رو مقدار داد، Plan مورد نیاز شما در Cache وجود داره و Reuse میشه، Compile اتفاق نمیافته. اگر کاربر دیگه ای Textbox دوم و چهارم رو پر کنه، باعث میشه یک Plan هم برای این حالت در Cache قرار بگیره و راه رو برای کاربرهای بعدی که چنین جستجویی دارند هموار کنه.
exec فقط در یک حالت میتونه Recompile نکنه: حالتی که عینا یک Query که قبلا اجرا شده، مجددا اجرا بشه بدون هیچگونه تغییر. یعنی حتی اگر یک Space هم در Query دوم تغییر ایجاد شده باشه، باید Recompile بشه. همچنین اگر مقداری که دارین براش جستجو میکنین تغییر کنه، باز هم Recompile نیاز داره.

[rezaei manesh]

با تشکرات ویژه از شما استاد گرانقدر
من جریان استفاده از sp های پویا که موجب می شد تا Reuse صورت نگیرد رو می دونستم البته sp هایی مثل مال من
به خاطر همین موضوع سعی می کردم که sp رو پویا ننوسیم در عوض از چندتا sp استفاده کنم و...
اما با توجه به مطالب کامل شما فهمیدم که می شه پروسیجر پویا نوشت که Reuse هم بشه و...
خوب من قبلا از این sp برای حذف رکورد ها استفاده می کردم


CREATE PROCEDURE SPDelete @StrIds NVARCHAR(100),@tblName nvarchar(30),@idName nvarchar(25)
AS
set nocount on
EXECUTE('DELETE FROM ' +@tblName+' WHERE '+ @idName+' IN (' + @StrIds + ')')
GO

اما حالا با توجه به فرمایشات شما من این کد رو به این شکل تغییر دادم آیا درست عمل کردم؟


CREATE PROCEDURE SPDelete @StrIds NVARCHAR(200),@tblName nvarchar(30),@idName nvarchar(25)
AS
set nocount on
declare @Param nvarchar(50)
declare @sql nvarchar(2000)
set @param=N'@xStrIds nvarchar(200)'
set @sql='DELETE FROM ' +@tblName+' WHERE '+ @idName+' IN ( @xStrIds )'

exec sp_executesql @sql,@param,@xStrIds=@StrIds
GO

[AminSobati]

IN پارامتر (یا متغیر) نمیپذیره که شما بخواین اون رو Parameterized کنین!

[rezaei manesh]

درسته کد بالا برای یک رکورد درست کار می کنه اما برای چندتا نه (به دلیل همین فرمایش شما)

[smt_414]

سلام جناب ثباتی
من این کد شما رو بررسی کردم ام چون در زمینه sql تازه کارم
سوال برام پیش اومد (البته سوالها شاید مربوط به کلاینت باشه ) اما چون کد رو اینجا دیدم , سوال رو همینجا طرح کردم
دو متغیر country ,city هر دو null تعریف شدند اگر بخواهیم از داخی برنامه(C#) به اونها مقدار بدیم (ارسال پارامتر) آیا پس از مقدار دادن به اونها در sp به جای مقدار null گذاشته میشه؟

سوال بعد
چه شکلی میشه در exec تنها از @country ,@city استفاده کرد یعنی اینطور بهشون مقدار نداد باید تنها از exec sp1 استفاده کرد؟

create proc sp1
@country varchar(20)=null,
@city varchar(20)=null
as

declare @sql nvarchar(max)
DECLARE @Param nvarchar(500)
set @sql='select * from customers where 1=1'
set @param=N'@xcity varchar(20), @xcountry varchar(20)'

if @country is not null
set @sql=@sql+' and country=@xcountry'

if @city is not null
set @sql=@sql+' and city=@xcity'

print @sql
exec sp_executesql @sql,@param,@xcity=@city,@xcountry=@country
go


exec sp1 @country='uk',@city='london'
exec sp1 @city='spain'
exec sp1 @country='uk'
exec sp1

[smt_414]

سلام
دوستان کسی می تونه راهنمایی کنه؟؟