Refine Search SQL with Custom Pagination

Question

Working with an asp.net mvc application and c#. We are working on a search page.

Updated with my Answer. Please see my own answer below and suggest

unfortunately select into is not working in sql azure http://blogs.msdn.com/b/sqlazure/archive/2010/05/04/10007212.aspx

Help me

I have the below tables to to show my item search result

Item Table

• ITEMID,SHOPID, ITEMNAME, DESCRIPTION, PRICE,CATID

Shop Table

• SHOPId, HEADER, CITYID, ACTIVE

City Table

• CITYID, CITYName,REGIONID,CountryISO

Category Table

• CATID, CATNAME

This is my Search Query that gives the paginated result for the given criteria

DECLARE @unitItems INT=20
DECLARE @sortOrder INT=0
DECLARE @catId INT
DECLARE @search NVARCHAR (100)=''
DECLARE @REGIONID INT=0
DECLARE @cityId INT=0
DECLARE @maxPrice DECIMAL (10, 2)
DECLARE @page INT
DECLARE @currentDate DATETIME2 (0)
set @unitItems=20
set @catId=0
set @sortOrder=0
set @search=''
set @cityId=1
set @maxPrice=0
set @page=1
set @currentDate='2013-02-24 13:14:58.073'

;WITH itemresult AS (
  SELECT IT.ITEMID, IT.ITEMNAME, IT.DESCRIPTION, IT.PRICE,
  IT.CATID, C.CATNAME AS CATNAME,S.HEADER AS SHOPHEADER,CI.CITYNAME AS CITY, 
  ROW_NUMBER() OVER (ORDER BY IT.SHOWDATE DESC) AS RowNumber
  FROM ITEM AS IT INNER JOIN SHOP AS S ON IT.SHOPId = S.ShopId 
  INNER JOIN CITY AS CI ON CI.CITYID = S.CITYID
  INNER JOIN COUNTRY AS CY ON CI.COUNTRYISO = CY.COUNTRYISO 
  INNER JOIN REGION AS R ON CI.REGIONID = R.REGIONID 
  INNER JOIN CITY AS CI2 ON CI2.CITYID = @cityId 
  INNER JOIN CATEGORY AS C ON IT.CATID = C.CATID    

  WHERE S.ACTIVE = 1   

  GROUP BY IT.ITEMID, IT.ITEMNAME, IT.HEADER, IT.DESCRIPTION, IT.PRICE, 
  IT.CATID, IT.SHOWDATE,S.HEADER,C.CATNAME,CI.CITYNAME) 

  SELECT IT.*, CEILING(CAST(RN AS float) / @unitItems) AS UNITPAGES, RN AS UNITROWS 
  FROM itemresult IT 
  INNER JOIN (SELECT Max(RowNumber) AS RN FROM itemresult) SUBQ ON 1=1 
  WHERE IT.RowNumber BETWEEN (@page - 1) * @unitItems + 1 
  AND @unitItems * @page 

Problem:

Now the problem is we are adding new changes in the UI. Search UI now will show something like below

First Result Set -->Assume 230 records found overall

Search Result as you see in Fiddle

Second Result Set -->Different Categories & Count from 230 records

CatId, CatName, TotalCountInSearch

Ex: 1 Books 25 & 2 Sports 43 & 8 Others 52. Show I can show like below in UI

• All Categories (120)
• Books (25)
• Sports (43)
• Others (52)

And Third Result Set -->Different Cities& Count from 230 records

CityId, CityName, TotalCountInSearch

for displaying below in UI

• All Cities(10)
• Chennai (4)
• Banglore (3)
• Others (3)

How can i retrieve these counts and names like all categories,books, allcities, etc? Any help or suggesstions are welcome.

Click and View SQL Fiddle here

I would like to get these counts based on the search criteria. wish to retreive this as another result set from my procedure

MAIN UPDATE:

I have all the schema and actual dynamic query uploaded here https://github.com/Padayappa/SQLProblem/blob/master/PaginationIssue

Answer 1

Try this. I hope this will satisfy your expection

CREATE VIEW vSequence AS
WITH itemresult AS (
  SELECT IT.ITEMID, IT.ITEMNAME, IT.DESCRIPTION, IT.PRICE,
  IT.CATID, C.CATNAME AS CATNAME,S.HEADER AS SHOPHEADER,CI.CITYNAME AS CITY,CI.CITYID,
  ROW_NUMBER() OVER (ORDER BY IT.SHOWDATE DESC) AS RowNumber
  FROM ITEM AS IT INNER JOIN SHOP AS S ON IT.SHOPId = S.ShopId
  INNER JOIN CITY AS CI ON CI.CITYID = S.CITYID
  INNER JOIN COUNTRY AS CY ON CI.COUNTRYISO = CY.COUNTRYISO
  INNER JOIN REGION AS R ON CI.REGIONID = R.REGIONID
  INNER JOIN CITY AS CI2 ON CI2.CITYID = 1
  INNER JOIN CATEGORY AS C ON IT.CATID = C.CATID

  WHERE S.ACTIVE = 1

  GROUP BY IT.ITEMID, IT.ITEMNAME, IT.HEADER, IT.DESCRIPTION, IT.PRICE,
  IT.CATID, IT.SHOWDATE,S.HEADER,C.CATNAME,CI.CITYNAME,CI.CITYID)

SELECT * FROM itemresult IT

GO

  SELECT IT.*, CEILING(CAST(RN AS float) / 20) AS UNITPAGES, RN AS UNITROWS
  FROM vSequence IT
  INNER JOIN (SELECT Max(RowNumber) AS RN FROM vSequence) SUBQ ON 1=1
  WHERE IT.RowNumber BETWEEN (1 - 1) * 20 + 1
  AND 20 * 1
GO
SELECT  IT.CATID,RS.CATNAME , CONVERT(varchar(10), SUM(CASE WHEN IT.CATID = CAT.CATID THEN 1 ELSE 0 END)) AS 'Count'
FROM vSequence RS INNER JOIN ITEM IT ON RS.CATID = IT.CATID
INNER JOIN CATEGORY CAT
ON IT.CATID = CAT.CATID GROUP BY IT.CATID,RS.CATNAME
GO
SELECT  CIT.CITYID,CITYNAME,CONVERT(varchar(10), SUM(CASE WHEN CIT.REGIONID =  REG.REGIONID THEN 1 ELSE 0 END)) AS 'Count'
FROM COUNTRY CON INNER JOIN REGION REG
ON CON.COUNTRYISO = REG.COUNTRYISO
INNER JOIN CITY CIT ON CIT.REGIONID =  REG.REGIONID
INNER JOIN  vSequence RS ON CIT.CITYID=RS.CITYID GROUP BY REG.REGIONID,CITYNAME, CIT.CITYID

SQL Fiddle sample.

Answer 2

As I understand from your comments, I think you need is to get 3 result sets by executing a single stored procedure. Also you want to know if the queries are optimised.

I think your vSequence VIEW is fine as it is (So, I am not adding here to my answer). What you need is to create a stored procedure to get three different result sets using your view as below. I have taken the variable list you have declared in the fiddle as parameters to the stored procedure. I have commented next to each variable. Since your filtering requirements are not clear I leave them as there are.

CREATE PROCEDURE myStoredProcedure 
   @unitItems INT=20, --number of items per page
   @sortOrder INT=0, --not used
   @catId INT, --not in use 
   @search NVARCHAR (100)='', --not used
   @REGIONID INT=0, -- not used
   @cityId INT=0, -- not used
   @maxPrice DECIMAL (10, 2), -- not used
   @page INT, --page number
   @currentDate DATETIME2 (0) -- not used
AS 
BEGIN

    /*
      Query 1
      Note: I have assumed your @page start at 1 and also changed the where clause 
      to bring correct data based on @page & @unitItems parameters 
    */
    SELECT IT.*, CEILING(CAST(RN AS float) / 20) AS UNITPAGES, RN AS UNITROWS
    FROM vSequence IT INNER JOIN (SELECT Max(RowNumber) AS RN FROM vSequence) SUBQ ON 1=1
    WHERE IT.RowNumber BETWEEN (@unitItems * (@page - 1) + 1) AND @unitItems 

    /* Query 2 */
    SELECT  IT.CATID,RS.CATNAME , CONVERT(varchar(10), 
            SUM(CASE WHEN IT.CATID = CAT.CATID THEN    1 ELSE 0 END)) AS 'Count'
    FROM vSequence RS 
              INNER JOIN ITEM IT ON RS.CATID = IT.CATID 
              INNER JOIN CATEGORY CAT ON IT.CATID = CAT.CATID 
    GROUP BY IT.CATID,RS.CATNAME

    /* Query 3 */
    SELECT  CIT.CITYID,CITYNAME,CONVERT(varchar(10), 
            SUM(CASE WHEN CIT.REGIONID =   REG.REGIONID THEN 1 ELSE 0 END)) AS 'Count'
    FROM COUNTRY CON 
             INNER JOIN REGION REG ON CON.COUNTRYISO = REG.COUNTRYISO
             INNER JOIN CITY CIT ON CIT.REGIONID =  REG.REGIONID
             INNER JOIN  vSequence RS ON CIT.CITYID=RS.CITYID 
    GROUP BY REG.REGIONID,CITYNAME, CIT.CITYID
 END

Below is how to execute the stored procedure in Management Studio (Please use appropriate values for parameters):

DECLARE @unitItems INT = 20, 
        @sortOrder INT = 0, 
        @catId INT = 0,
        @search NVARCHAR (100) = '', 
        @REGIONID INT = 0, 
        @cityId INT = 1,
        @maxPrice DECIMAL (10, 2) = 0,
        @page INT = 1,
        @currentDate DATETIME2 (0) = '2013-02-24 13:14:58.073'

EXEC myStoredProcedure 
       @unitItems,
       @sortOrder,
       @catId,
       @search,
       @REGIONID,
       @cityId,
       @maxPrice,
       @page,
       @currentDate

To execute this stored procedure within your C# code use a parameterised query with a DataAdapter as below:

DataSet ds = new DataSet();
using (SqlConnection connection =  new SqlConnection("your-Connection-String-here"))
{

    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand("myStoredProcedure", connection);
    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    adapter.SelectCommand.Parameters.AddWithValue("@unitItems",20);
    //add other parameters as above here
    adapter.SelectCommand.Parameters.AddWithValue("@page",1); //correct page number
    adapter.Fill(ds);
}

//Now you can access all query results as 
ds.Tables[0]; //results from query1
ds.Tables[1]; //results from query2
ds.Tables[2]; //results from query3