ایجاد جستجو چندتایی با Store Procedure

[jas1387]

با عرض سلام خدمت دوستان

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

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

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

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

[Reza_Yarahmadi]

یک راه اینه که همه پارامترها رو از نوع nvarchar بگیری و قبل از Select چک کنی ببینی خالی هستند یا نه ، اگر خالی بود کاراکتر % رو قرار بدی.

if (@Param1 = NULL)

     Set @Param1 = '%'

[s.khoshfekran]

به نظر من Store Procedure رو اینجوری تعریف کنی بهتر باشه!
موفق باشی

USE Northwind

GO

create proc sp_1 @FiledName nvarchar(20), @Text nvarchar(20)

as

Select * from Employees Where @FiledName = @Text

[jas1387]

یک راه اینه که همه پارامترها رو از نوع 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]

سلام

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

[Reza_Yarahmadi]

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]

تا جایی که میدونم این خطا بخاطر تعریف نکردن پارامتر توی برنامه است و ربطی به SP نداره. کد برنامه رو چک کنید ببینید پارامتر Lname@ رو تعریف کردید یا نه.

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

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

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

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

[اوبالیت به بو]

برادر برای اینکه کارت راه بیوفته،

یه همچین حرکتی استاد مباحث ویژه زد که خوراک مخصوص 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]

برادر برای اینکه کارت راه بیوفته،

یه همچین حرکتی استاد مباحث ویژه زد که خوراک مخصوص 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]

بفرما
EXEC sp_executesq
دوای دردته. میتونی پارام ها رو مقدار اولیه هم بکنی.خودت تستش کن. آسونه. اگه سوالی بود من بیدارم

[jas1387]

سلام دوست عزیز

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

set @select = @select + @where

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

EXEC sp_executesql @select

[Rejnev]

سلام
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]

دوست عزیز از وقتی که گذاشتید برای حل این مسئله ممنون هستم

من اومد و مطابق کد شما کدهای خودم را تغییردادم و همچنین پارامترهای ورودی هم کم کردم (برای راحتی کار ، مشکل که حل شد دوباره پارامترها را برمی گردونم ) این هم 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]

البته درست نمیدونم ولی اگه بیای تعریف 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]

create proc search
@name nvarchar(50) , @id int
as
select * from
where
(@name = '' or name = @name)
and (@id = 0 or id = @id)

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

[jas1387]

create proc search
@name nvarchar(50) , @id int
as
select * from
where
(@name = '' or name = @name)
and (@id = 0 or id = @id)

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

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

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

[jas1387]

هنوز من منتظر جواب این سوال هستم

[mrsalam]

اینو من نوشتم و برای اطمینان تست کردم

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]

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

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

ممنون

[mrsalam]

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

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

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

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

P1="%"+txtTest.Text.Trim()+"%";

P1=>'%%' خواهد بود

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