مشکل در استورد پروسیجر در ساختن کوئری با شرط

[saeed_sho]

سلام
من یه استورد پروسیجر نوشتم برای سلکت کردن و برای جستجو ازش استفاده میکنم و معلوم نیست کاربر چند تا از مقدار ها رو وارد میکند بنابراین اومدم براش کوئری ساختم با چندتا شرط اگر هیچ پارامتری مقداردهی نشه درسته اما اگه پارامتر @price رو مقداردهی کنم که از نوع bigint هستش این ارورو میده
Error converting data type nvarchar to bigint.
اینم بگم که استورد پروسیجر رو در محیط اس کیو ال اجرا کردم یعنی روی استورد پروسیجر راست کلیک و execute رو زدم
ورژن مورد استفاده sql server 2008 r2 و زبان برنامه نویسی asp.net

اینم کدهای استورد پروسیجر
ALTER Procedure [dbo].[SP_charge_card_selectSearch]
@expdate smalldatetime=Null,
@createDate smalldatetime=Null,
@price bigint=Null
As

Declare @SQL nvarchar(max)
Set @SQL = 'SELECT [cc_id]
,[cc_serial]
,[cc_pass]
,[cc_price]
,[dbo].[MiladiToShamsi]([cc_expdate]) as cc_expdate
,[cc_is_used]
,[dbo].[MiladiToShamsi]([cc_createDate]) as cc_createDate
FROM [sf_db].[sfuser].[tbl_charge_card]'

If (@expdate Is Not Null Or @createDate Is Not Null Or @price Is Not Null)
Begin
Set @SQL = @SQL + ' Where '
End

If (@expdate Is Not Null)
Begin
Set @SQL = @SQL + 'cc_expdate = ' + @expdate
End

If (@expdate Is Not Null)
Begin
Set @SQL = @SQL + ' And '
End

If (@createDate Is Not Null)
Begin
Set @SQL = @SQL + 'cc_createDate = ' + @createDate
End

If (@expdate Is Not Null Or @createDate Is Not Null)
Begin
Set @SQL = @SQL + ' And '
End

If (@price Is Not Null)
Begin
Set @SQL = @SQL + 'cc_price = ' + @price
End

Exec sp_executesql @SQL

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

[saeed_sho]

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

[baktash.n81@gmail.com]

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

[mohsen24000]

از تابع ISNULL استفاده کنید...
http://barnamenevis.org/showthread.php?366770-%DA%86%D8%B1%D8%A7-Dynamic-Query-%D8%9F-%DA%86%D8%B1%D8%A7-IsNull-%D9%86%D9%87-%D8%9F!&highlight=isnull

[ali.bahrami]

دوست عزیز شما بیایید در انتها کل دستور را داخل یک متغیر رشته ای بزارید و توسط print اونو چاپ کنید ببینید ساختار دستور شما بعد از اجرای شرط ها چگونه است ... اونوقت بهتر میشه متوجه ایراد شد.

[saeed_sho]

روش مناسب تری هم وجود داره برای وقتی که نمی دونیم کاربر چند تا از پارامتر ها رو می خواد پر کنه ... جستجو بفرمایید
دوست عزیز شما راه حل رو پیشنهاد بدین من خودم جستجو میکنم
روش آقای تاجیک رو امتحان میکنم که لینک تاپیکشون رو mohsen24000 گذاشتن اما اگه راه بهتری هست ممنون میشم پیشنهاد بدین

[saeed_sho]

من از روش آقای تاجیک استفاده کردم و 90% درست شده که به یاری شما دوستان بقیه ش هم درست میشه
این کدهای منه که تغییر دادم و انصافا چقدر با کد قبلی تفاوت داره خوب همونجور که میبینید دوتا پارامتر از نوع تاریخ دارم که تاریخو باید قبل و بعدش باید کوتیشن بذارم و به استورد پروسیجر پاس بدم یعنی اینجوری '14/11/2012' که اگه نذارم ارور میده
incorrect syntax near '/'
اینم بگم که استورد پروسیجر رو در اس کیو ال تست میکنم با راست کلیک و execute
من نمیخوام در ویژوال استودیو موقع پاس دادن پارامتر به استورد پروسیجر از کوتیشن استفاده کنم
حالا سؤال اینه چطوری میشه قبل و بعد پارامتر کوتیشن گذاشت من اینو میذارم ''' (3 تا کوتیشن) اما جواب نداد
لطفا راهنمایی کنید تا تاپیک به نتیجه برسه فقط تاریخش اذیت میکنه
ALTER Procedure [dbo].[SP_charge_card_selectSearch]
@FromDate smalldatetime=Null,
@ToDate smalldatetime=Null,
@price bigint=Null
As

SELECT [cc_id]
,[cc_serial]
,[cc_pass]
,[cc_price]
,[dbo].[MiladiToShamsi]([cc_expdate]) as cc_expdate
,[cc_is_used]
,[dbo].[MiladiToShamsi]([cc_createDate]) as cc_createDate
FROM [sf_db].[sfuser].[tbl_charge_card]
Where
[cc_price] = IsNull(@price, [cc_price])
And
[cc_createDate] >= IsNull(@FromDate, [cc_createDate])
And
[cc_createDate] <= IsNull(@ToDate, [cc_createDate])

[saeed_sho]

از QuoteName(@FromDate) هم استفاده کردم نشد
لطفا راهنمایی کنید چطوری قبل و بعد پارامترهای تاریخ کوتیشن بذارم از 3 تا کوتیشن هم استفاده کردم نشد
نمیخوام در asp.net پارامترو با کوتیشن پاس بدم اینجوری فلسفه ی استورد پروسیجر که امنیت هست میره زیر سؤال

[saeed_sho]

دوست عزیز شما بیایید در انتها کل دستور را داخل یک متغیر رشته ای بزارید و توسط print اونو چاپ کنید ببینید ساختار دستور شما بعد از اجرای شرط ها چگونه است ... اونوقت بهتر میشه متوجه ایراد شد.
اگه تاریخو بدون کوتیشن بدم در کوئری جدید ارور میده
incorrect syntax near '/'
که اگه پرینت بگیرم ارورو برمیگردونه

[Felony]

از QuoteName(@FromDate) هم استفاده کردم نشد
لطفا راهنمایی کنید چطوری قبل و بعد پارامترهای تاریخ کوتیشن بذارم از 3 تا کوتیشن هم استفاده کردم نشد
نمیخوام در asp.net پارامترو با کوتیشن پاس بدم اینجوری فلسفه ی استورد پروسیجر که امنیت هست میره زیر سؤال
يعني چي كه فلسفه SP زير سوال ميره ؟! :لبخندساده:
شما بايد اطلاعات رو به صورت يك Data Type استاندارد بين برنامه ها مبادله كنيد ، حالا اومديم و ما راهنمايي كرديم و اول و آخر پارامتر پاس داداه شده يك جفت ' انداختي ، ميشه بگي اون تاريخي كه ميخواي بين ASP و SQL مبادله كني چي هست ؟! ميخواي از ASP تاريخ رو به صورت عدد پاس بدي ؟ به صورت Object پاس بدي ؟!

[saeed_sho]

اول باید بگم که راه حلتون فوق العاده بود ممنونم خیلی وقت بود دیگه از توابع استفاده نمیکردم که باید خودمو اصلاح کنم :خجالت:
فلسفه ی استورد پروسیجر ایجاد امنیت هست برای اینکه دیگه مقدارها رو در ویژوال در سینگل کوتیشن نذاریم و کوئری رو هم مهاجمین نبینن تا مورد سوء استفاده قرار نگیره اگه قرار باشه مقداری رو که به پارامتر استورد پروسیجر پاس میدیم رو بذاریم در کوتیشن خب دیگه فرقی نداره با نوشتن کامل کوئری
همونطور در کدها که میبینید پارامترها از نوع smalldatetime هستن و در ویژوال تاریخو اینجوری میدم
date1.tostring("d")
date1 از نوع datetime در asp.net هست
البته بازم میگم در ویژوال تست نکردم و در اس کیو ال با راست کلیک و execute اجرا کردم که اینو بدم درست اجرا میشه '14/11/2012' اما اینو بدم ارور میده 14/11/2012 ارور incorrect syntax near '/'
فکر کنم در ویژوال استودیو درست کار کنه باید تست کنم
حالا شما لطف کنید گذاشتن کوتیشن رو بگید اگه در ویژوال استودیو درست کار نکرد بزارم
بعد از اجرا در ویژوال استودیو جوابشو اینجا میزارم
تا فدا جوابشو میزارم

[Felony]

فلسفه ی استورد پروسیجر ایجاد امنیت هست برای اینکه دیگه مقدارها رو در ویژوال در سینگل کوتیشن نذاریم و کوئری رو هم مهاجمین نبینن تا مورد سوء استفاده قرار نگیره اگه قرار باشه مقداری رو که به پارامتر استورد پروسیجر پاس میدیم رو بذاریم در کوتیشن خب دیگه فرقی نداره با نوشتن کامل کوئری
این مورد امنیت رو زیر سوال نمیبره ، مثلا برای پروسه Authenticate کردن یک User چه راهکاری ارائه میدید ؟ Username و Password اون رو چه طور به یک SP احراز هویت پاس میدید ؟!

والا من نمیدونم تو ویژوال استادیو Dataset ها چه متدهایی در اختیارتون میزارن ، مثلا ما تو دلفی برای پاس دادن پارامتر میتونیم نوع اون پارامتر رو مشخص کنیم ، مثلا برای تاریخ :

StoredProcedure1.Parameters.paramByName('@Date').A sDateTime := DateTimePicker.Date;

حتما باید تو Dataset های ارائه شده توسط VS هم همچین متدهایی پیاده شده باشه .

[saeed_sho]

همین الان سریع در ویژوال استودیو تست کردم پارامترو بصورت datetime پاس دادم درست کار کرد وبه این نتیجه رسیدم که دیگه استورد پروسیجر رو در اس کیو ال تست نکنم
از همه دوستان عذر میخوام من واقعا شرمندم :خجالت:
کوئری درسته و کولاکه


والا من نمیدونم تو ویژوال استادیو Dataset ها چه متدهایی در اختیارتون میزارن ، مثلا ما تو دلفی برای پاس دادن پارامتر میتونیم نوع اون پارامتر رو مشخص کنیم ، مثلا برای تاریخ :

1
StoredProcedure1.Parameters.paramByName('@Date').A sDateTime := DateTimePicker.Date;





حتما باید تو Dataset های ارائه شده توسط VS هم همچین متدهایی پیاده شده باشه .

بله آقای تاجیک یه کد خیلی شبیه به اون داره
oledbDataAdapter1.selectcommand.parameters.add("fromdate",oledbtype.date).value=date1.tostring("d")


این مورد امنیت رو زیر سوال نمیبره ، مثلا برای پروسه Authenticate کردن یک User چه راهکاری ارائه میدید ؟ Username و Password اون رو چه طور به یک SP احراز هویت پاس میدید ؟!
این سؤالو باید جواب بدم یا مثال زدید؟

با سوتی که دادم فکر کنم تا یه هفته دیگه روم نشه در این تالار پست بزارم

[saeed_sho]

از همه دوستانی که لطف کردن و راهنمایی کردن ممنونم تاپیک به نتیجه رسید اینم کوئری درست
ALTER Procedure [dbo].[SP_charge_card_selectSearch]
@FromDate smalldatetime=Null,
@ToDate smalldatetime=Null,
@price bigint=Null
As

SELECT [cc_id]
,[cc_serial]
,[cc_pass]
,[cc_price]
,[dbo].[MiladiToShamsi]([cc_expdate]) as cc_expdate
,[cc_is_used]
,[dbo].[MiladiToShamsi]([cc_createDate]) as cc_createDate
FROM [sf_db].[sfuser].[tbl_charge_card]
Where
[cc_price] = IsNull(@price, [cc_price])
And
[cc_createDate] >= IsNull(@FromDate, [cc_createDate])
And
[cc_createDate] <= IsNull(@ToDate, [cc_createDate])