PDA

View Full Version : ایجاد جستجو چندتایی با Store Procedure



jas1387
شنبه 28 فروردین 1389, 09:00 صبح
با عرض سلام خدمت دوستان

من توی برنامه ام یه جای دارم که کاربر می تونه از چهارتا textbox که گذاشتم (نام ، نام خانوادگی ، نام پدر و شماره شناسنامه ) هر کدومش را پر بکنه و جستجو را انجام بده

حالا یه store Procedure نوشتم که چهار پارامتر ورودی داره و براساس اون چهار پارامتر جستجو محدود میشه (راستی برای ترکیب پارامترها توی whereدستور select از and استفاده کردم) وقتی تمام پارمتراها مقدار دهی بشود درست کار می کند ولی اگه یکی از اون ها مقدار دهی نشود کار نمی کند چرا کار کنم که کار کند ؟

می خواستم که از دستور if استفاده کنم و ببینم که اگر پارامترها مقدار دهی نشده بود توی شرط دستور select نیاورم که با طرز استفاده آن در store Procedure آشنا نبودم

راستی میشه پارامترها را store Procedure اختیاری تعریف کرد ؟ که اگر مقدار دهی نشده اون را جز شرط دستور Select به حساب نیاورد ؟

Reza_Yarahmadi
شنبه 28 فروردین 1389, 11:08 صبح
یک راه اینه که همه پارامترها رو از نوع nvarchar بگیری و قبل از Select چک کنی ببینی خالی هستند یا نه ، اگر خالی بود کاراکتر % رو قرار بدی.


if (@Param1 = NULL)
Set @Param1 = '%'

s.khoshfekran
شنبه 28 فروردین 1389, 11:15 صبح
به نظر من Store Procedure رو اینجوری تعریف کنی بهتر باشه!
موفق باشی



USE Northwind
GO
create proc sp_1 @FiledName nvarchar(20), @Text nvarchar(20)
as
Select * from Employees Where @FiledName = @Text

jas1387
شنبه 28 فروردین 1389, 14:00 عصر
یک راه اینه که همه پارامترها رو از نوع nvarchar بگیری و قبل از Select چک کنی ببینی خالی هستند یا نه ، اگر خالی بود کاراکتر % رو قرار بدی.


if (@Param1 = NULL)
Set @Param1 = '%'


سلام دوست عزیز کد شما به نظر منطقی می آمد ولی این خطا را میده ، چرا ؟





Msg 201, Level 16, State 4, Procedure sp_ViewHomecustomer_SelectByID, Line 0
Procedure or Function 'sp_ViewHomecustomer_SelectByID' expects parameter '@Lname', which was not supplied.


این هم stored procedure من هست.



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[sp_ViewHomecustomer_SelectByID]
@Fname as nvarchar(max),
@Lname as nvarchar(max),
@Father as nvarchar(50),
@Natioalid as nchar


AS
BEGIN

if (@Fname is NULL)
Set @Fname = '%'
if (@Lname = NULL)
Set @Lname = '%'
if (@Father = NULL)
Set @Father = '%'
if (@Natioalid = NULL)
Set @Natioalid = '%'

select Hom_ID,Cus_ID,Cus_Firstname as 'نام',Cus_Lastname as 'نام خانوادگی',Cus_Father as 'نام پدر',Cus_Nationalid as 'شماره ملی',
Cus_Phone,Cus_address,Hom_Date as 'تاریخ',Hom_Doc as 'شماره فیش',Hom_Pricebond,Hom_Countbond,Hom_Creditor as 'بستانکار',Hom_Debtor as 'بدهکار',Hom_Comment
from view_Home_Customer
where Cus_Firstname like @Fname and Cus_Lastname like @Lname and Cus_Father like @Father and Cus_Nationalid like @Natioalid

END

jas1387
شنبه 28 فروردین 1389, 18:25 عصر
سلام

از اساتید محترم کسی نیست به من کمک کنه ممنون می شوم

Reza_Yarahmadi
شنبه 28 فروردین 1389, 20:39 عصر
Msg 201, Level 16, State 4, Procedure sp_ViewHomecustomer_SelectByID, Line 0a
Procedure or Function 'sp_ViewHomecustomer_SelectByID' expects parameter '@Lname', which was not supplied.
تا جایی که میدونم این خطا بخاطر تعریف نکردن پارامتر توی برنامه است و ربطی به SP نداره. کد برنامه رو چک کنید ببینید پارامتر Lname@ رو تعریف کردید یا نه.

jas1387
شنبه 28 فروردین 1389, 20:58 عصر
تا جایی که میدونم این خطا بخاطر تعریف نکردن پارامتر توی برنامه است و ربطی به SP نداره. کد برنامه رو چک کنید ببینید پارامتر Lname@ رو تعریف کردید یا نه.

دوست عزیز کد Stored procedure را من گذاشتم و همانطور که می بینید من پارامتر Lname@ را تعریف کردم

خطا بالا را زمانی رخ می دهد که من روی Stored procedure راست کلیک می کنم و Execute Stored procedure را می زنم

من تا امشب وقت دارم لطفا به من کمک کنید

یه چیز دیگه من وقتی Stored procedure را اجرا کردم و خطا داد فقط فیلد Fname@ را مقدار دهی کردم و اگه فیلد Lname@ را مقدار دهی کنم این دفعه به فیلد @Father گیر میده

اوبالیت به بو
شنبه 28 فروردین 1389, 22:50 عصر
برادر برای اینکه کارت راه بیوفته،

یه همچین حرکتی استاد مباحث ویژه زد که خوراک مخصوص vb6 کارهاست واسه در رفتن. فعلا همین رو استفاده کن تا بعد. اگه تونستی بیارش تو t-SQL:


string strSQL = string.Empty;
string strSearch = "Select * FROM Table WHERE";
if (ProductName != null )
strSQL += " AND ProductName=" + ProductName;

if (Discription != null)
strSQL += " AND Discription=" + Discription;

if (Price != null)
strSQL += " AND Price=" + Price;

strSearch += strSQL.Substring(3, strSQL.Length - 3);

میاد یه سری شرط می زاره که اگر فیلد هاش (textBoxها) خالی نبودن و فلان و فلان رشته AND Field=Value رو می چسبونه به رشته دستور SQL.
در آخر هم برای اینکه خطای syntax نده (WHERE AND Field=Value که نمیشه) میاد AND و space رو برمی داره.

اینجوری یه دستور SQL می سازی. من فکر کنم مشکل شما همین باشه یعنی جستجوی ترکیبی

jas1387
شنبه 28 فروردین 1389, 23:31 عصر
برادر برای اینکه کارت راه بیوفته،

یه همچین حرکتی استاد مباحث ویژه زد که خوراک مخصوص vb6 کارهاست واسه در رفتن. فعلا همین رو استفاده کن تا بعد. اگه تونستی بیارش تو t-SQL:


string strSQL = string.Empty;
string strSearch = "Select * FROM Table WHERE";
if (ProductName != null )
strSQL += " AND ProductName=" + ProductName;

if (Discription != null)
strSQL += " AND Discription=" + Discription;

if (Price != null)
strSQL += " AND Price=" + Price;

strSearch += strSQL.Substring(3, strSQL.Length - 3);
میاد یه سری شرط می زاره که اگر فیلد هاش (textBoxها) خالی نبودن و فلان و فلان رشته AND Field=Value رو می چسبونه به رشته دستور SQL.
در آخر هم برای اینکه خطای syntax نده (WHERE AND Field=Value که نمیشه) میاد AND و space رو برمی داره.

اینجوری یه دستور SQL می سازی. من فکر کنم مشکل شما همین باشه یعنی جستجوی ترکیبی

دوست عزیز من از Stored Procedure استفاده می کنم و راه حلی توی Stored Procedure میخواهم

Rejnev
شنبه 28 فروردین 1389, 23:38 عصر
بفرما
EXEC sp_executesq
دوای دردته. میتونی پارام ها رو مقدار اولیه هم بکنی.خودت تستش کن. آسونه. اگه سوالی بود من بیدارم

jas1387
شنبه 28 فروردین 1389, 23:51 عصر
سلام دوست عزیز

بابت کد خیلی ممنون
من حال ندارم جدول درست کنم وکد شما را اجرا کنم ولی خواهم از کد شما الگو بگیریم و کد خود را مانند شما کنم دوتا سوال
1.این کد آیا می آید دستور select و where را در متغیر select می ریزد ؟


set @select = @select + @where

2.کد زیر برای چه چیزی هست و آیا من که کدم را در C# اجرا می کنم نیازی به نوشتن این دستور دارم ؟


EXEC sp_executesql @select

Rejnev
یک شنبه 29 فروردین 1389, 00:00 صبح
سلام
1- بله. در واقع رشته where رو به انتهای Select میچسبونه
2- وقتی proc رو نوشتی، اجرا کن تا در sql ایجاد بشه. بعد باید نام proc رو در C#‎‎‎‎ صدا بزنی و پارامتر بدی مثلا:



sqlCommand cmd=new sqlCommand();
cmd.CommandText="procName"
cmd.Parameters.AddWithValue(...);
...
cmd.execueNone...

یا بیای به روش وصله پینه ای صدا بزنی





sqlCommand cmd=new sqlCommand();
cmd.CommandText="exec procName '"+ txtName.text+"' "+...
...
if(txtFamily!="")
{
cmd.CommandText+=","+txtFamily.Text
}
cmd.execueNone...

jas1387
یک شنبه 29 فروردین 1389, 00:15 صبح
دوست عزیز از وقتی که گذاشتید برای حل این مسئله ممنون هستم

من اومد و مطابق کد شما کدهای خودم را تغییردادم و همچنین پارامترهای ورودی هم کم کردم (برای راحتی کار ، مشکل که حل شد دوباره پارامترها را برمی گردونم ) این هم SP من با الگو گیری از شما





ALTER PROCEDURE [dbo].[sp_ViewHomecustomer_SelectByID]
@Fname as nvarchar(max),
@Lname as nvarchar(max)

AS
Declare @select nvarchar(1000)
Declare @where nvarchar(500)
BEGIN




set @select='select * from view_Home_Customer '

if(@Fname is not null)
if(@where is null)
set @where = ' where Cus_Firstname = '''+ @Fname +''''
else
set @where = @where + ' and Cus_Firstname = '''+@Fname+''''
else if (@Lname is not null)
if(@where is null)
set @where = ' where Cus_Lastname = '''+@Lname+''''
else
set @where = @where + ' and Cus_Lastname = '''+@Lname+''''
set @select = @select + @where
END




ولی باز خطا میده




Msg 201, Level 16, State 4, Procedure sp_ViewHomecustomer_SelectByID, Line 0
Procedure or Function 'sp_ViewHomecustomer_SelectByID' expects parameter '@Lname', which was not supplied.

Rejnev
یک شنبه 29 فروردین 1389, 00:32 صبح
البته درست نمیدونم ولی اگه بیای تعریف select , where رو داخل begin end بذاری بهتره.
بعد برای تعریف پارام as رو حذف کن

Exec sp...
رو هم یادت نره
اصلا یک بار sp رو drop کن
مقدار اولیه هنگام تعریف پارام بده مثلا:



@Fname as nvarchar(max)=null,
@Lname as nvarchar(max)=null


که اگه کاربر در دستور مقدار نداد خطا نگیره.
در ضمن اگه خواستی از یک پارام پرش کنی مثلا سه تا داری میخوای اولی و سومی رو بدی باید پارام دومی رو در سی شارپ برابر Default بگیری که یک کلمه رزرو شده است در sql. خلاصه سعی کن پارام ها در Sql مقدار اولیه داشته باشن. مثل همین کد بالایی منظورمه.
مثال در سی شارپ



cmd.CommandText="exec procName ";
if(...)
cmd.CommandText+=txtfName.Text;
if(...)
cmd.CommandText+=","+txtlName.Text;
else
cmd.CommandText+=",default"
if(...)
cmd.CommandText+=","+txtAddress.Text;
else...

meitti
یک شنبه 29 فروردین 1389, 09:33 صبح
create proc search
@name nvarchar(50) , @id int
as
select * from
where
(@name = '' or name = @name)
and (@id = 0 or id = @id)

اگه منظورتون رو درست فهمیده باشم

jas1387
دوشنبه 30 فروردین 1389, 18:04 عصر
create proc search
@name nvarchar(50) , @id int
as
select * from
where
(@name = '' or name = @name)
and (@id = 0 or id = @id)

اگه منظورتون رو درست فهمیده باشم





این هم جواب نداد

من که از روش دیگری استفاده کردم ولی می خواهم این روش رو یاد بگیرم از دوستان اگه یه فایل کوچیک SQL بگذارند ممنون می شم

jas1387
چهارشنبه 01 اردیبهشت 1389, 13:19 عصر
هنوز من منتظر جواب این سوال هستم

:متفکر:

mrsalam
چهارشنبه 01 اردیبهشت 1389, 14:30 عصر
اینو من نوشتم و برای اطمینان تست کردم


ALTER PROCEDURE [dbo].[spSearch]
(
@n nvarchar(20),
@ecode nvarchar(20),
@rcode nvarchar(20)

)
AS
SET NOCOUNT ON;
SELECT id, [name], eCode, rCode, pCode, tell, fax, address
FROM tCustomer
WHERE ((name LIKE @n)and (eCode like @ecode)and (rCode like @rcode))




تو c# اینجوری مقدار بده:

eCode="%"+txtTest.Text+"%"

jas1387
پنج شنبه 02 اردیبهشت 1389, 09:15 صبح
باز هم جواب نداد

دوستان عریز لطف کنید و یه فایل Stored procedure را که در یکی از جدول های خود sql server جستجو انجام می دهد را برای من ایجاد کرده و بفرستید تا ببینم که اشکال از کجاست ، sql من مشکل داره یا .....

ممنون

mrsalam
پنج شنبه 02 اردیبهشت 1389, 09:49 صبح
باز هم جواب نداد


من اینو تو پروژه هام استفاده می کنم و کاملا تست شده و صحیح است ، ظاهرا شما با کدهای خود SQL آشنا نیستین که در ادامه توضیح مختصری می نویسم:

ببینید من در شرط کوری بجای = از LIKE استفاده کردم
حال در قسمت ارسال پارامتر به رویه باید به ابتدا و انتهای پارامترمان یک علامت % اضافه نمائیم.

نتیجه اینکه در صورتی که پارامتر مقدار تهی داشت(کاربر مقداری مشخص نکرده) ، با توجه به اینکه همیشه قبل از ارسال پارامتر به رویه ، علامت % درج می نمائیم ، مقادیر تهی به شکل زیر ارسال می گردد:

P1="%"+txtTest.Text.Trim()+"%";
P1=>'%%' خواهد بود

راستی این رو هم مد نظر داشته باش که متغیرها از نوع NvarChar باشند
راستی اگر مشکلت رفع نشد می تونی از طریق chat سوال کنی