tazarvmmr
دوشنبه 15 آذر 1389, 06:53 صبح
سلام به همه
دوستان این 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
دوستان این 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