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

[tazarvmmr]

سلام به همه

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

فقط یک توضیح اینکه وقتی من مقدار recordCount@ رو غیر null بفرستم تعداد رکوردها رو بر میگردونه و وقتی null بفرستم خود رکوردها رو

حالا مشکل اینجاست که در شرایط یکسیان تعداد رکوردها رو 11 تا برمیگردونه ولی خود رکوردها رو که برمیگردونه فقط 7 تاس! :گیج::عصبانی++::اشتباه: :ناراحت:

دو روزه کلافه شدم! شاید اشکالش هم آخرش یه چیز مسخره باشه ولی من دیگه چشمام نمیبینه!
ممنون میشم یه بررسی بکنبن



set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
go



ALTER Procedure [dbo].[sp_SearchProjectsBasedOnOffices]

@TypeIncluded bit,
@TypeId int = -1,
@Keyword nvarchar(200) = null,
@UserId int,
@startRow int = -1,
@maxRows int = -1,
@sortOrder nvarchar(50) = null,
@recordCount int = null output


as
Declare @whereSQL nvarchar (500)
Declare @SQL nvarchar (4000)
Declare @UserGroupAdmin int
Declare @UserIsAdmin bit

Select @UserIsAdmin = UserIsAdmin From Users Where Id = @UserId
Select @UserGroupAdmin = UserGroupCode From Users Where Id = @UserId And UserGroupAdmin = 1

if @UserGroupAdmin is null set @UserGroupAdmin = -1
IF (@Keyword is null) set @Keyword = ''
IF @UserIsAdmin =1 Begin Set @whereSQL = "(ProjectRelatedOffices.OfficeIsActiveInProject = 1) AND (RequestedProjects.Allowed = 1) AND (Offices.OfficeName Like N'%" +@Keyword+ "%') "
goto Here
End
If @UserGroupAdmin > 0 Begin Set @whereSQL = "(ProjectRelatedOffices.OfficeIsActiveInProject = 1) AND (RequestedProjects.Allowed = 1) AND (Offices.OfficeName Like N'%" +@Keyword+ "%') and (RequestedProjects.RequestedSectionCode = " + CONVERT(nvarchar(10), @UserGroupAdmin) + " or Users.UserGroupCode = " + CONVERT(nvarchar(10), @UserGroupAdmin) + ") "
goto Here
End
If @UserGroupAdmin = -1 Begin Set @whereSQL = "(ProjectRelatedOffices.OfficeIsActiveInProject = 1) AND (RequestedProjects.Allowed = 1) AND (Offices.OfficeName Like N'%" +@Keyword+ "%') and (RequestedProjects.RequestedUserCode = " + CONVERT(nvarchar(10), @UserId) + " or ProjectNames.ProjectNextUserCode = " + CONVERT(nvarchar(10), @UserId) + ") "
End

Here:

IF @TypeIncluded = 1 Set @whereSQL = @whereSQL + " and (ProjectRelatedOffices.OfficePositionInProjectId = " + CONVERT(nvarchar(10), @TypeID) + ")"


IF (@sortOrder is null) or (@sortOrder = '') set @sortOrder = 'OfficeName'

IF @recordCount is not null
Begin
Declare @ParamDef nvarchar(100)
Declare @retval int
set @SQL = " Select @retval = COUNT( DISTINCT Offices.Id)
FROM ProjectNames LEFT OUTER JOIN
RequestedProjects ON ProjectNames.RequestId = RequestedProjects.Id LEFT OUTER JOIN
Users ON ProjectNames.ProjectNextUserCode = Users.Id RIGHT OUTER JOIN
ProjectRelatedOffices ON ProjectNames.Id = ProjectRelatedOffices.ProjectNameId RIGHT OUTER JOIN
Offices ON ProjectRelatedOffices.OfficeId = Offices.Id LEFT OUTER JOIN
CustomersPfix ON Offices.OfficeSuffixId = CustomersPfix.Id
Where " + @whereSQL
Set @ParamDef = '@retval int output'
EXECUTE sp_executesql @SQL, @paramDef, @retval Output
Select @recordCount = @retVal
Return
End

Set @SQL = " Select DISTINCT OfficeFullName, OfficeName, OfficeId
From ( SELECT DISTINCT
Offices.Id AS OfficeId, Offices.OfficeName, ISNULL(CustomersPfix.CustomerPfixCaption, '') + N' ' + Offices.OfficeName AS OfficeFullName, Row_Number() Over (Order by Offices.OfficeName) as RowNum
FROM ProjectNames LEFT OUTER JOIN
RequestedProjects ON ProjectNames.RequestId = RequestedProjects.Id LEFT OUTER JOIN
Users ON ProjectNames.ProjectNextUserCode = Users.Id RIGHT OUTER JOIN
ProjectRelatedOffices ON ProjectNames.Id = ProjectRelatedOffices.ProjectNameId RIGHT OUTER JOIN
Offices ON ProjectRelatedOffices.OfficeId = Offices.Id LEFT OUTER JOIN
CustomersPfix ON Offices.OfficeSuffixId = CustomersPfix.Id
Where " + @whereSQL + " ) as officeInfo
Where (RowNum between " + CONVERT(nvarchar(10), @startRow) + " AND " + CONVERT(nvarchar(10), @startRow) + " + " + CONVERT(nvarchar(10), @maxRows) + " - 1
OR " + CONVERT(nvarchar(10), @startRow) + " = -1 OR " + CONVERT(nvarchar(10), @maxRows) + " = -1)
Order by " + @sortOrder

EXEC sp_executesql @SQL

return

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

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

[tazarvmmr]

مرسی از توجهتون
اینم دیتا بیس (http://www.2shared.com/file/DQ-PU1zB/database_test_.html)
فقط توضیح اینکه شما وقتی execute میکنید با این پارامترها صدا کنید باید تعداد رو 11 تا برگردونه:

@typeIncluded = 0
@typeId = 1
@userId = 5
@recordCount =0


حالا برای خود رکوردها با این پارامترها صدا کنین:

@typeIncluded = 0
@typeId = 1
@userId = 5
@startRow = 0
@maxRows = 20


در این حالت تعداد یازده رکوردو برمیگردونه ولی اگر به جای maxRows عدد 11 رو بزارید یعنی دقیقا همون تعدادی که خود تابع اعلام کرده و یا 10 که فرض کنیم page size هستش فقط 7 تا یا 8 تاشو بر میگردونه!

من که دیگه گیج شدم!

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

ببینید الان این قسمت کد شما داره رکوردهای تکراری برمیگردونه.
اگر میشه در مورد این کد مقداری توضیح بدید.که قراره چه چیزی رو برگردونه


SELECT Offices.Id AS OfficeId, Offices.OfficeName,
ISNULL(CustomersPfix.CustomerPfixCaption, '') + N' ' + Offices.OfficeName AS OfficeFullName ,
Row_Number() Over (Order by Offices.OfficeName) as RowNum
FROM ProjectNames LEFT OUTER JOIN
RequestedProjects
ON ProjectNames.RequestId = RequestedProjects.Id
LEFT OUTER JOIN Users
ON ProjectNames.ProjectNextUserCode = Users.Id
RIGHT OUTER JOIN ProjectRelatedOffices
ON ProjectNames.Id = ProjectRelatedOffices.ProjectNameId
RIGHT OUTER JOIN Offices
ON ProjectRelatedOffices.OfficeId = Offices.Id
LEFT OUTER JOIN CustomersPfix
ON Offices.OfficeSuffixId = CustomersPfix.Id
Where (ProjectRelatedOffices.OfficeIsActiveInProject = 1) AND (RequestedProjects.Allowed = 1)
AND (Offices.OfficeName Like N'%%')
and (RequestedProjects.RequestedUserCode = 5 or ProjectNames.ProjectNextUserCode = 5)


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

بعد این کوئری رو اجرا کنید.



Select DISTINCT OfficeFullName, OfficeName, OfficeId,officeInfo.RowNum
From (SELECT Offices.Id AS OfficeId, Offices.OfficeName,
ISNULL(CustomersPfix.CustomerPfixCaption, '') + N' ' + Offices.OfficeName AS OfficeFullName ,
Row_Number() Over (Order by Offices.OfficeName) as RowNum
FROM ProjectNames LEFT OUTER JOIN
RequestedProjects
ON ProjectNames.RequestId = RequestedProjects.Id
LEFT OUTER JOIN Users
ON ProjectNames.ProjectNextUserCode = Users.Id
RIGHT OUTER JOIN ProjectRelatedOffices
ON ProjectNames.Id = ProjectRelatedOffices.ProjectNameId
RIGHT OUTER JOIN Offices
ON ProjectRelatedOffices.OfficeId = Offices.Id
LEFT OUTER JOIN CustomersPfix
ON Offices.OfficeSuffixId = CustomersPfix.Id
Where (ProjectRelatedOffices.OfficeIsActiveInProject = 1) AND (RequestedProjects.Allowed = 1)
AND (Offices.OfficeName Like N'%%')
and (RequestedProjects.RequestedUserCode = 5 or ProjectNames.ProjectNextUserCode = 5)) as officeInfo
Where (RowNum between 0 AND 0 + 11 - 1 OR 0 = -1 OR 11 = -1)
Order by OfficeName


به خاطر این 7-8 رکورد برمیگردونه چون رکورد تکراری داره و چون شما ROWNUM رو قرار ندادید کل رکوردهارو نمیده.ولی من این فیلد رو قرار دادم همه رکوردهارو میده ولی تکراری برمیگردونه که اون کوئری اولی که پست کردم رو باید نگاه کنید.

[tazarvmmr]

سلام
قبل از اینکه اجرا کنم این کد رو اون توضیحی که خواستید رو میدم، ما یکسری office داریم که تو جدول offices هستند، حالا ممکنه با هر کدوم از این office ها یک یا چند پروژه داشته باشیم، که ما کد هر Office و کد پروژه رو تو جدول ProjectRelatedOffices میریزیم، بنابراین ممکنه کد یک office بارها در جدول فوق تکرارشده باشه البته با کدهای پروژه متفاوت.
حالا این پراسیجر کلا قراره اسم office هایی که ما باهاشون پروژه داریم رو برگردونه بصورت غیر تکراری، وقتی اسم یک office رو داد بعدا ما با یک پراسیجر دیگه اسم و تعداد پروژه هایی که با این office داریمو از روی office id برمیگردونیم.
در حقیقت پارامتر keyWord قسمتی از اسم office هست که ما میخواهیم بر اساس اون جستجو کنیم ولی چون user ها حقوق دسترسی برابر ندارن چک کردیم که اول تعیین بکنه محدوده دیدشون چیه و شرط جستجو رو تغییر بده برای هر نوع user و این قسمت باعث پیچیدگی شده
من الان هر دو تا کد رو اجرا کردم توی دومی چون شما RowNum رو قرار دادین ما اسم office تکراری داریم مثل "آبادگران" من به خاطر همین که جلوی این تکرار رو بگیرم RowNum رو نشون ندادم
حالا یک سوال برای من پیش اومده، چرا ما وقتی مقدار maxRows رو عوض میکنیم تعداد ردیفها بی قاعده عوض میشن! در صورتیکه هر دوتا شرط در شمارش و نمایش یکیه! باورتون میشه اصلا دیگه نمیتونم بفهمم کدوم عدد درسته! یعنی نمیدونم اشکال در شمارش هست که عدد 11 رو بر میگردونه یا در نمایش که 7 تا نمایش میده!

راستی اون قسمتی که فرمودید تکراری برمیگردنه، شما Distinct رو برداشتید، تو اونی که من فرستادم داشت!

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

اون کوئری اول که براتون ارسال کردم درواقع کوئری درون کوئری دومی هست که ارسال کردم.

[tazarvmmr]

بله، درسته
در حقیقت با select بالایی تکرارها حذف میشن، تو این کوئری به نظرم هر کاری کنیم تکراری وجود داره چون RowNum هست و باعث میشه ما تکرار داشته باشیم ولی Select Distinct قبلی تکراری ها رو نشون نمیده و به خاطر اینکه تکرار نداشته باشیم من RowNum رو نمایش ندادم

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

خوب مشکل همینجاست.
مثلا فرض کنید بعضی از رکوردها شماره rownumber اونا 7 یا 8 هست و شما گفتید تا 11 نمایش بده و تکراری هارو نیاره. به خاطر همین درست به شما نمایش نمیده و تعداد رو کم نمایش میده.
باید کوئری دوم که row number هم داره جوری نوشته بشه که رکوردهای تکراری رو نیاره.
اگر وقت کنم سعی میکنم اونو براتون اصلاح میکنم.

[tazarvmmr]

خیلی ممنون، من خودم هم از این راهنمایی استفاده میکنم و سعی میکنم تغییرش بدم، به نظرم حرفتون کاملا درسته و جواب همه ابهامات منو در مورد maxRows هم میده، گمون میکنم اگر من بیام چک کنم که OfficeId در جدول ProjectsRelatedOffice هست مشکل حل بشه!


Where Offices.Id In(Select OfficeId From ProjectsRelatedOffices)
بعنی اصلا تکنیک نوشتنشو عوض کنم
نظرتون چیه؟

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

فکر میکنم با افزودن این مورد مشکلتون حل بشه. اگر نشد باید روی تکنیکش مقداری فکر کرد.شاید بشه حتی بهینه تر هم نوشت