دریافت خطا در ارسال پارامتر به SP

[احمد سامعی]

سلام
من می خوام چندتا پارامتر به صورت دینامیک برای جستجو در بانک به sp ارسال کنم اما خط می شده یک sp به صورت زیر و ساده نوشتم اما از همین هم خطا می گیره


ALTER PROCEDURE dbo.mySP
@exp int
AS
declare @query varchar(120)
set @query = 'SELECT country FROM myTb WHERE (dateExp > @exp) ORDER BY ID DESC'

exec(@query)

RETURN

این هم خطا : که می گه پارامتر ناشناخته هست؟

Must declare the scalar variable "@exp".

[Davood_amega]

مشکلی نداشت و Stored Procedure را درست کرد .

[احمد سامعی]

کد که مشکلی نداره

زمان اجرا خطا می ده

[ahmad_eagle2002]

اگر به صورت زير اصلاح كنيد فكر مي كنم كار كند

@query = 'SELECT country FROM myTb WHERE (dateExp > '+cast (@exp as varchar)+') ORDER BY ID DESC'

[محمد سلیم آبادی]

این sp که نوشتین اصلا نیازی به بحث داینامیک نداره. ولی برای داینامیک کردن باید مقدار متغیر را با رشته الحاق کنید همانطوری که در پست قبلی بهش اشاره شده.
به فرض اگر بخواهین یک لیست داینامیک را در کوئری بکار بگیرین میتونید از همین تکنیکی که بهش اشاره کردین استفاده کنید. به بحث انتهایی این مقاله (http://www.30sharp.com/ShowArticle.aspx?nid=13&did=212&AuthorID=11) می تونید رجوع کنید.

[AminSobati]

سلام دوست عزیزم،
علاوه بر توضیحات مفید دوستان، دیدن این مقاله هم برای جستجوی داینامیک مفید هست:
http://www.sommarskog.se/dyn-search.html

[احمد سامعی]

شاید بد توضیح دادم من می خوام جستجو دینامیک داشته باشم

تو انجمن و اینترنت هرچی سرچ زدم به همون روش مقاله آقای ثباتی ختم شد که نتونستم کاربردی کنم


علاوه بر توضیحات مفید دوستان، دیدن این مقاله هم برای جستجوی داینامیک مفید هست:
سلام جناب ثباتی
این لینک قبلاً تو یک پست دیگه هم شما داده بودید و خوندم و از همون روش استفاده کردم اما جواب نگرفتم (نمی دونم کجا رو اشتباه می نویسم؟ )

این sp که نوشتین اصلا نیازی به بحث داینامیک نداره.

شاید من زیادی بد توضیح دادم، این sp رو برای نمونه نوشتم در واقع من می خوام همون جستجو دینامیک داشته باشم و بر اساس چند پارامتر سرچ بزنم که ممکن بعضی از اونها یا همش در زمان اجرا خالی باشه اما نتونستم حتی یک پارامتر به sp پاس بدم چه برسه به چندتا ؟

مقاله جناب سلیم آبادی در مورد جستجوی دینامیک نبود اگر در این مورد مقاله دارید بسیار ممنون می شم لینک بدید


exp as varchar

ببخشید دوست عزیز اگر توجه می کردید فیلد من عدد هست نه varchar

[محمد سلیم آبادی]

اگر تعداد پارامتر هایی که بر اساس آن سطرها فیلتر میشن متغیر هست میتونید به شکل زیر عمل کنید. یا اینکه sp اصلی اتون را پست کنید و کامل سناریو را توضیح بدین.

SELECT * FROM table
WHERE (col1 = @par1 OR @par1 IS NULL) AND
(col2 = @par2 OR @par2 IS NULL) , .... ;

[محمد سلیم آبادی]

ببخشید دوست عزیز اگر توجه می کردید فیلد من عدد هست نه varchar
اتفاقا اینشون به این موضوع توجه کردن. و به همین علت تبدیل به رشته کردن تا بتونند به رشته الحاقش بدن.

[احمد سامعی]

باز هم ممنون استاد
من از همین روش پست 8 هم استفاده کردم اما چون بعضی از پارامترها عدد و بعضی رشته هست قبول نمی کنه و مقدار خالی بازگشت داده می شه (برای اعداد مقدار منفی یک گذاشتم چون مقدار صفر در جودل داریم)

سناریو خیلی ساده هست.
یک جدول با چندین فیلد : در جستجو های مختلف کاربر بر اساس چندین فیلد می تونه فیلترینگ داشته باشه از هیچ فیلتر یعنی بازگشت همه سطرها تا فیلتر روی یک یا دو یا چند فیلد مختلف

[احمد سامعی]

اتفاقا اینشون به این موضوع توجه کردن. و به همین علت تبدیل به رشته کردن تا بتونند به رشته الحاقش بدن.

اما خطا چیز دیگه ای می گه

Must declare the scalar variable "@exp".
پیغام عدم تعریف شدن پارامتر می ده ؟

[محمد سلیم آبادی]

اما خطا چیز دیگه ای می گه

Must declare the scalar variable "@exp".
پیغام عدم تعریف شدن پارامتر می ده ؟

راه حلی که در پست 8 قرار داده شده مناسب برای هر نوع data type ای هست بهتر هست کد اصلی اتان را پست کنید تا بشه کامل بررسی کرد.

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

[AminSobati]

دوست عزیزم علت خطای Must Declare این هست که شما متغیر رو در خارج از EXEC تعریف کردین. میتونین این روش رو در پیش بگیرین:



exec ('use northwind; declare @x int; set @x=10249; select * from orders where orderid=@x')


ولی اگر از sp_executesql استفاده کنین، دیگه موضوع متفاوت میشه و Variable رو در قالب Parameter ارسال خواهید کرد. حدس میزنم از sp_executesql به شکل درست استفاده نکردین

[محمد سلیم آبادی]

میتونین این روش رو در پیش بگیرین
این روش بکارش نمیاد چرا که مقدار توسط پارامتر ورودی sp مقدار دهی میشه...
باید مقدار متغیر را با رشته الحاق کنند که اینکار انجام داده نشده.

[ZingaZung]

سلام
شاید این sp بتونه این مشلکو حل کنه چون خود من هم چند وقت پیش داخل یک win App به چنین سوالی بر خوردم در واقع می خواستم یک سرچ روی یک جدل بزنم بر اساس ترکیب های مختلفی از ستون ها که نتیجش شد دستور پایین



create procedure [dbo].[sp_SearchCoGeneralInfo](@fileNo int=null,@coName nvarchar(80)=null,
@branchName nvarchar(30)=null,@indicator int=null)
as
begin

select fileNo,Coname,branchName,indicator
from tblCoGeneralInfo

where fileNo=isnull(@fileno,fileno) and
(coname like '%'+isnull(@coName,coname)+'%') and
(branchName like '%'+isnull(@branchName,branchName)+'%')and
indicator=isnull(@indicator,indicator)

end

[محمد سلیم آبادی]

سلام،
تکنیکی که استفاده شده در کوئری خیلی جالب و خلاصه شده هست.
منظورم نوشتن گزاره زیر به جای گزاره بعدی:


ID = ISNULL(@id, ID)



(ID = @id OR @id IS NULL)

[احمد سامعی]

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

جناب سلیم آبادی من در پست اول و هشتم توضیح دادم:

من یک جدول دارم با چندین Column حالا می خوام تو این جدول یک جستجو بزنم که در حالت های مختلف هست یعنی کاربر ممکن هربار یک یا چند تا از Column ها رو فیلتر کنه من یک نمونه از چیزی که می خوام در پایین نوشتم : فکر نمی کنم کاری می خوام انجام بدم چیز جدید باشه یک کوئری طبیعی در هر دیتابیسی هست


CREATE PROCEDURE dbo.SearchSp
@title varchar(50)
@subject varchar(50),
@date int,
@dateExp int,
@state smallint,
@Islock bit
AS
select Title,Txt,Subject,DateExp,State,IsLock from newTb
where (Title = isnull(@title, Title))
and (Subject = isnull(@subject, Subject)
and (State = isnull(@state, State)
and (IsLock = isnull(@Islock, IsLock)
RETURN

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

اما اگر در برنامه یک یا چند تا از پارامتر ها مقدار null داشته باشن پیغام خطای زیر داده می شه


Procedure or function 'SearchSp' expects parameter '@title', which was not supplied.

[محمد سلیم آبادی]

شما برای پارامتر های ورودی مقدار پیشفرض null در نظر نگرفتین.
به تعریف پارامتر های این SP توجه کنید:
http://www.30sharp.com/article/13/252/11/%d8%aa%da%a9%d9%86%db%8c%da%a9%db%8c-%d8%a8%d8%b3%db%8c%d8%a7%d8%b1-%d8%b3%d8%a7%d8%af%d9%87-%d9%88-%da%a9%d8%a7%d8%b1%d8%a8%d8%b1%d8%af%db%8c-%d8%a8%d8%b1%d8%a7%db%8c-%d8%ac%d8%b3%d8%aa%d8%ac%d9%88%db%8c-%d9%be%d9%88%db%8c%d8%a7.aspx

[احمد سامعی]

سلام استاد سلیم آبادی

با تشکر فراوان از جواب های شما

من همانطور که در مقاله گفتید عمل کردم اما پارامترهای عددی یا بولین رو از داخل برنامه nullبزارم هیچ رکوردی برگشت نمی شه این هم بخشی از کد برنامه :


Command.Parameters.Add("@state", SqlDbType.SmallInt).Value = null;
if (state > 0) { Command.Parameters[2].Value = state; }

راهی که به نظرم رسید اینکه از داخل sp دوباره مقدار بررسی کنم اگر برابر null بود دوباره برابر null کنم به شکل زیر


IF ( @state = NULL )
SET @state = NULL
اما فکر نمی کنم این راه درستی باشه

[محمد سلیم آبادی]

سلام،
من راجب کد داخل برنامه اتان نمی تونم کمکی بکنم. شما اگر نیاز به پارامتری ندارین اصلا نیاز به مقدار دهی اون با null وجود نداره.
کدی که در تگ دوم قرار دادین (if) بی معناست. چرا که:
1. مقادیر Null را با عملگر مخصوص خود یعنی is null می سنجن.
2. متغیرها بطور پیشفرض null هستند و همچنین در SP که معرفی کردم مقدار null را مقدار پیشفرض تایین کردم.
3. شما متغیری که null هست را می خواهید به null تغییر دهید! یعنی خودش را با خودش جایگزین کردن معنا ندارد.

در کل بهتر است سوالتان را در بخشی که کد برنامه اتان به آن مرتبط هست مطرح کنید. مثلا تالار سی شارپ

[احمد سامعی]

باز هم ممنون از وقتی که گذاشتید استاد

اما اگر دقت کرده باشید در شرطی که if بررسی می کنه ما از برنامه مقدار nuul دریافت کردیم که دوباره با مقدار null داره ست می شه یعنی برنامه داره درست عمل می کنه و مقدار null به sp پاس می کنه. اما اگر این کد که من نوشتم از sp حذف بشه جواب نمی ده. یعنی مشکل در برنامه نیست چون با همین روش وقتی تایپ از نوع رشته باشه مشکلی نیست اما برای عددی نه!!


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

به هر حال با همین روش خنده دار غیر منطقی کد پست قبلی ام :قهقهه::متفکر: داره کار می کنه ولی من که نفهمیدم چطوری؟:متفکر:
بگذریم از وقتی که گذاشتید باز هم ممنون

[AminSobati]

سلام،
تکنیکی که استفاده شده در کوئری خیلی جالب و خلاصه شده هست.
منظورم نوشتن گزاره زیر به جای گزاره بعدی:


ID = ISNULL(@id, ID)



(ID = @id OR @id IS NULL)


روش قشنگیه، حیف که Table Scan میده

[محمد سلیم آبادی]

حیف که Table Scan میده
اوه، به این موضوع توجه نکرده بودم. پس همون روش شناخته شده مناسب هست.

[احمد سامعی]

سلام
ببخشید اساتید عزیز اما مثل اینکه این سوال ادامه داره

1. جناب ثباتی معایب Table Scan چیه ( لینکی چیزی ... من سرچ کردم مطلبی نیافتم :گریه: تا روش های شناخت که در کجا اعمال می شه و روش های پرهیزش رو بدانم)

2. با توجه لینک شما در پست 6 من خواستم از روش if و exec استفاده کنم اما وقتی می خوام از کوئری خروجی بگیرم که در همون sp یا sp دیگه ای استفاده کنم خطا می ده
من sp خودم دو تا کردم یعنی با یکی select لازم انجام دادم و خواستم مقدار به دومی پاس کنم که مقدار پیش فرض برگشت می داد در صورتی که وقتی خود همون sp اولی انجام می دم مقدار برام از جدول دریافت می کنه
من نمونه sp می زارم

CREATTE PROCEDURE dbo.Front
@name char(2) = NULL,
@exp int,
@PageIndex int OUTPUT
AS
DECLARE @StartIndex int
set @StartIndex = (@PageIndex * 15) + 1

DECLARE @EndIndex int
set @EndIndex = (@PageIndex * 15) + 15

DECLARE @query1 varchar(500)
SET @query1 = 'SELECT * FROM (SELECT ID, name, title, dateE, photo, row_id = ROW_NUMBER() OVER(ORDER BY Front.ID DESC) FROM Front'

DECLARE @query2 varchar(100)
SET @query2 = ') AS D WHERE D.row_id BETWEEN ' + str(@StartIndex) + ' AND ' + str(@EndIndex)

DECLARE @query3 varchar(50)
IF ( @name IS NULL )
SET @query3 = null
ELSE SET @query3 = ' WHERE name = ' + quotename(@name, '''') + ' AND dateE >= ' + + str(@exp)

EXEC ( @query1 + @query3 + @query2 )

IF ( @name IS NULL )
SELECT @PageIndex = CAST(COUNT(*) AS INT) FROM Front
ELSE SELECT @PageIndex = CAST(COUNT(*) AS INT) FROM Front WHERE (name = @name) AND ([dateE] >= @exp)

در صورتی که از روش IS NULL مشکلی نداره و هچنین اگر SP که مقدار می خوام بخونم استاتیک باشه باز هم مقدار صحیح برگشت می شه

آخرین SELECT همون چیزی که می خوام برگشت بشه به همراه نتیجه کوئری اولی اما چون جواب نگرفتم اینطوری نوشتم
البته در این نمونه تعداد بخش های where کم هست اما در کوئری اصلی زیاد هستند


اوه، به این موضوع توجه نکرده بودم. پس همون روش شناخته شده مناسب هست.

منظور کدام روش هست اینقدر راه حل دوستان گفتن من گیج شدم

باز هم ممنون از صبر و بزرگواری اساتید

[AminSobati]

Table Scan به این معنی هست که SQL Server نمیدونه رکورد مورد جستجو کجا قرار گرفته پس مجبوره کل جدول رو بخونه. ولی استفاده از ایندکس مناسب باعث میشه به جای Scan، عمل Seek رخ بده که IO بسیار پایین تری داره. نوشتن کوئری مهم هست چون بعضی کارها در کوئری باعث میشه همیشه Table Scan در پیش گرفته بشه و شانس استفاده از ایندکس رو از دست بدین. مثل:


ID = ISNULL(@id, ID)