tejal bhesaniya

NA
53
16.3k

Gettinng error in Stored procedure

Oct 31 2013 8:28 AM

Hello all here i am giving the script of my procedure and as well as error i m not getting why this error is comin

exec sp_Product_details_for_Productcategory_as_per_productcategoryID 216,2,1,1,''

=================== Procedure -======================

Create Proc [dbo].[sp_Product_details_for_Productcategory_as_per_productcategoryID]

@ProductCategoryId int,

@PageIndex INT

,@PageSize INT

,@PageCount INT OUTPUT

,@ProductSearch nvarchar(Max)

BEGIN

declare @Criteria varchar(4000)

set @Criteria = (select replace(@ProductSearch,'(','('''))

set @Criteria = (select REPLACE(@Criteria,',',''','''))

set @Criteria = (select REPLACE(@Criteria,')',''')'))

declare @sql int

SET NOCOUNT ON;

select @sql = ' SELECT ROW_NUMBER() OVER

(

ORDER BY PM.ProductMainPkId ASC

)AS RowNumber ,

PM.ProductMainPkId ProductMainPkId

,PM.Title,

PS.ProductSubCategory ProductSubCategory,

PM.ProductSubCategoryFkId ,

PP.MarketValue,

PP.DiscountPrice,

PID.Path1Thumb

INTO #Results

from ProductMain_Details PM

Left join ProductPrice_Details PP on PM.ProductMainPkId = PP.ProductMain_FkId

Left join ProductImage_Details PID on PM.ProductMainPkId = PID.ProductMain_FkId

Left Join ProductSubCategory_Master PS on PM.ProductSubCategoryFkId = PS.ProductSubCategoryPkId

where

--PS.ProductSubCategorypkId = 216

PS.ProductSubCategorypkId = '+ CAST((@ProductCategoryId) as varchar(5) ) +' and

PM.Active = 1

and PM.Deleted = 0'

+@Criteria+'

DECLARE @RecordCount INT

SELECT @RecordCount = COUNT(*) FROM #Results

SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

PRINT @PageCount

SELECT * FROM #Results

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

DROP TABLE #Results'

EXEC (@sql)

END

===================================== Error ==============================

Msg 245, Level 16, State 1, Procedure sp_Product_details_for_Productcategory_as_per_productcategoryID, Line 21

Conversion failed when converting the varchar value ' SELECT ROW_NUMBER() OVER

(