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(CA
c# asp.net sql sql-server sql-server-2008
Edit
Report