I am trying to write an SQL script, but I am getting unexpected results. The @TotalResults give me 6 as the records, when I know there are 33 records returned.
Here's the code:
SELECT @TotalPages = CEILING(COUNT(a.MemberID)/@PageSize), @TotalResults = COUNT(a.MemberID) FROM Member a INNER JOIN MemberBusinessCat b ON b.MemberID = a.MemberID INNER JOIN BusinessCat c ON c.BusinessCatID = b.BusinessCatID WHERE a.SystemID = @SystemID AND c.CategoryName LIKE '%' + @SearchStr + '%' AND ( @ShowUnclaimed != 'N' OR ( a.Claimed = 'Y' AND a.SBIcon = 'N' ) ) AND a.Viewable = 'Y' GROUP BY a.MemberID, a.CreateDate, a.UserName, a.PrCity, a.MemberDisplayName, a.PrStateID, a.PrPhone, a.ShortDesc, a.PrCountryID; WITH CoalPrepCategorySearch AS ( SELECT ROW_NUMBER() OVER(ORDER BY a.MemberDisplayName ASC) AS RowNum, a.MemberID, a.UserName, a.PrCity, a.PrStateID, a.PrPhone, @TotalPages AS TotalPages, a.MemberDisplayName AS DisplayName, a.ShortDesc, @TotalResults AS TotalResults, a.PrCountryID FROM Member a INNER JOIN MemberBusinessCat b ON b.MemberID = a.MemberID INNER JOIN BusinessCat c ON c.BusinessCatID = b.BusinessCatID WHERE a.SystemID = @SystemID AND c.CategoryName LIKE '%' + @SearchStr + '%' AND ( @ShowUnclaimed != 'N' OR ( a.Claimed = 'Y' AND a.SBIcon = 'N' ) ) AND a.Viewable = 'Y' GROUP BY a.MemberID, a.CreateDate, a.UserName, a.PrCity, a.MemberDisplayName, a.PrStateID, a.PrPhone, a.ShortDesc, a.PrCountryID ) SELECT * FROM CoalPrepCategorySearch WHERE RowNum BETWEEN (@PG - 1) * @PageSize + 1 AND @PG * @PageSize ORDER BY DisplayName ASC I am pretty sure it is related to the grouping. If it is, then how can I get the total results? What am I doing wrong?
Many thanks in advance.
neojakey
