Error in Query?

Question

USE [SiteDemo]

GO

/****** Object: StoredProcedure [dbo].[sp_Master_SubCategoryInfo_SelectALLByCategoryName] Script Date: 09/15/2014 09:24:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

alter proc [dbo].[sp_Master_ProductInfoFilter]

@PageIndex INT = 1

,@PageSize INT = 10

,@SubCategoryName nvarchar(max)=null

,@RecordCount INT OUTPUT

AS

BEGIN

declare @Query nvarchar(MAX)

SET NOCOUNT ON;

set @Query= 'SELECT'+ ROW_NUMBER()OVER +'

(

ORDER BY [ID]desc

) AS'+ RowNumber +'

,[ID]

,[StoreName]

,[ProductName]

,[ProductBrand]

,[OldPrice]

,[NewPrice]

,[Discount]

,[ProductDiscription]

,[ProductRatings]

,[ProductFeatures]

,[ProductSiteLink]

,[ProductCategory]

,[ProductSubCategory]

,[Link]

,[ProductBannerImage]

,[ProductHoverImage]

,[ProductVideoLink]

,[ProductOverView]

,[ProductAdditionalInfo]

,[ProductIsActive]

,[ProductIsFeatured]

,[ProductIsNewArrival]

,[ProductIsBestSeller]

,[ProductInStock]

,[IsLatest]

,[AvailableSizes]

,[AvailableColors]

,[CouponCode]

,[CreatedBy]

,[CreatedOn]

,[ModifiedBy]

,[ModifiedOn] INTO #Results FROM [Master_ProductInfo] '+ @SubCategoryName + ' '+ 'SELECT @RecordCount = COUNT(*) FROM #Results

SELECT * FROM #Results

WHERE RowNumber BETWEEN '+ (@PageIndex -1) * @PageSize + 1 +' AND'+ (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 +'

DROP TABLE #Results'

END