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,''
Create Proc [dbo].[sp_Product_details_for_Productcategory_as_per_productcategoryID]
@ProductCategoryId int,
@PageIndex INT
,@PageSize INT
,@PageCount INT OUTPUT
,@ProductSearch nvarchar(Max)
as
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
(
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 = 216 and
PM.Active = 1
and PM.Deleted = 0
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' to data type int.