I have executed above sql in SQL Server 2005 and 2008R2 and got same error message as like..
Running the sql above returns the following on 2005
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ProductLoadAllPaged'.
(1 row(s) affected)It's seems above syntax is not supported by SQL Server 2008R2 and lower version.
This error occurred when
ExecuteStoredProcedureList function called in code base.
Most possible solution is:Add
Exec keyword just before
ProductLoadAllPaged in ProductServices.cs file.
var products = _dbContext.ExecuteStoredProcedureList<Product>(
"Exec ProductLoadAllPaged",
pCategoryIds,
pManufacturerId,
pStoreId,
pVendorId,
pWarehouseId,
pParentGroupedProductId,
pProductTypeId,
pVisibleIndividuallyOnly,
pProductTagId,
pFeaturedProducts,
pPriceMin,
pPriceMax,
pKeywords,
pSearchDescriptions,
pSearchSku,
pSearchProductTags,
pUseFullTextSearch,
pFullTextMode,
pFilteredSpecs,
pLanguageId,
pOrderBy,
pAllowedCustomerRoleIds,
pPageIndex,
pPageSize,
pShowHidden,
pLoadFilterableSpecificationAttributeOptionIds,
pFilterableSpecificationAttributeOptionIds,
pTotalRecords);
It would produce below sql and execute without any syntax error.
declare @p29 nvarchar(max)
set @p29=NULL
declare @p30 int
set @p30=0
exec sp_executesql N'exec ProductLoadAllPaged @CategoryIds, @ManufacturerId, @StoreId, @VendorId, @WarehouseId, @ParentGroupedProductId, @ProductTypeId, @VisibleIndividuallyOnly, @ProductTagId, @FeaturedProducts, @PriceMin, @PriceMax, @Keywords, @SearchDescriptions, @SearchSku, @SearchProductTags, @UseFullTextSearch, @FullTextMode, @FilteredSpecs, @LanguageId, @OrderBy, @AllowedCustomerRoleIds, @PageIndex, @PageSize, @ShowHidden, @LoadFilterableSpecificationAttributeOptionIds, @FilterableSpecificationAttributeOptionIds output, @TotalRecords output',N'@CategoryIds nvarchar(4000),@ManufacturerId int,@StoreId int,@VendorId int,@WarehouseId int,@ParentGroupedProductId int,@ProductTypeId int,@VisibleIndividuallyOnly int,@ProductTagId int,@FeaturedProducts bit,@PriceMin decimal(29,0),@PriceMax decimal(29,0),@Keywords nvarchar(4),@SearchDescriptions bit,@SearchSku bit,@SearchProductTags bit,@UseFullTextSearch bit,@FullTextMode int,@FilteredSpecs nvarchar(4000),@LanguageId int,@OrderBy int,@AllowedCustomerRoleIds nvarchar(5),@PageIndex int,@PageSize int,@ShowHidden bit,@LoadFilterableSpecificationAttributeOptionIds bit,@FilterableSpecificationAttributeOptionIds nvarchar(max) output,@TotalRecords int output',@CategoryIds=N'',@ManufacturerId=0,@StoreId=0, @VendorId=0, @WarehouseId=0,@ParentGroupedProductId=0, @ProductTypeId=NULL,@VisibleIndividuallyOnly=0, @ProductTagId=0,@FeaturedProducts=NULL,@PriceMin=NULL,@PriceMax=NULL, @Keywords=N'test',@SearchDescriptions=0,@SearchSku=1,@SearchProductTags=0,@UseFullTextSearch=0, @FullTextMode=0, @FilteredSpecs=N'',@LanguageId=0,@OrderBy=0,@AllowedCustomerRoleIds=N'1,2,3',@PageIndex=0,@PageSize=15, @ShowHidden=1,@LoadFilterableSpecificationAttributeOptionIds=0,@FilterableSpecificationAttributeOptionIds=@p29 output,@TotalRecords=@p30 output
select @p29, @p30