Hey everyone,
I'm having an issue with a SQL Procedure and am stumped as to what to do. The procedure works fine when I install the module on DNN 4.9.4 but gives me an error on DNN 5.1.2
Is there something I'm doing wrong? or has something changed between version 4 and 5 that is preventing this query from running?
............DNN 5.1.2 Error................
Error: is currently unavailable.
DotNetNuke.Services.Exceptions.ModuleLoadException: Operand data type nvarchar is invalid for minus operator. ---> System.Data.SqlClient.SqlException: Operand data type nvarchar is invalid for minus operator. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at DotNetNuke.Common.Utilities.CBO.FillListFromReader(Type objType, IDataReader dr, IList objList, Boolean closeReader) at DotNetNuke.Common.Utilities.CBO.FillCollection(IDataReader dr, Type objType) at LSpaceDesign.DNN.ArticleManagement.Business.ArticleCategoryController.List(Int32 portalID) at LSpaceDesign.DNN.ArticleManagement.CategoryEditor.EnsureParentCategoryListIsRetrieved() at LSpaceDesign.DNN.ArticleManagement.CategoryEditor.BindGrid(Int32 editID) at LSpaceDesign.DNN.ArticleManagement.CategoryEditor.Page_Load(Object sender, EventArgs e) --- End of inner exception stack trace ---
............Procedure.........
GO
CREATE PROCEDURE [dbo].[ArticleCategoryListPaginated]
@portalID int,
@pageSize int,
@pageIndex int,
@sortOrder nvarchar(500),
@reverseSortOrder nvarchar(500)
AS
BEGIN
DECLARE @sql nvarchar(4000),
@recCount int,
@skip int,
@toFetch int
SELECT @recCount = COUNT(*)
FROM dbo.ArticleCategory
WHERE PortalID = @PortalID
SET @skip = @pageIndex * @pageSize
SET @toFetch = @recCount - @skip
SET @toFetch = CASE
WHEN @toFetch > @pageSize THEN @pageSize
WHEN @toFetch < 0 THEN 0
ELSE @toFetch
END
SELECT TotalRecordCount = @recCount
SET @sql = 'SELECT * ' +
'FROM (SELECT TOP ' + CAST(STR(@toFetch) as nvarchar(10)) + ' * ' +
'FROM (SELECT TOP ' + CAST(STR(@toFetch + @skip) as nvarchar(10)) + ' * ' +
'FROM (SELECT TOP ' + CAST(STR(@recCount) as nvarchar(10)) + ' ' +
'ac.[ArticleCategoryID], ' +
'ac.[PortalID], ' +
'ac.[CategoryName], ' +
'ac.[CategoryDisplayName], ' +
'ac.[Searchable], ' +
'ac.[ParentCategoryID], ' +
'ac.[UseForFiltering], ' +
'ac.[CategoryTabID], ' +
'[Path] = dbo.ArticleCategoryGetPath(ac.ArticleCategoryID, '' > ''), ' +
'[DisplayPath] = dbo.ArticleCategoryGetDisplayPath(ac.ArticleCategoryID, '' > ''), ' +
'[InheritedCategoryTabID] = dbo.ArticleCategoryGetCategoryTabID(ac.ArticleCategoryID), ' +
'[Depth] = dbo.ArticleCategoryGetDepth(ac.ArticleCategoryID), ' +
'[ParentCategoryName] = pc.CategoryName ' +
'FROM dbo.ArticleCategory ac ' +
'LEFT JOIN dbo.ArticleCategory pc ' +
'ON ac.ParentCategoryID = pc.ArticleCategoryID ' +
'WHERE ac.PortalID = @PortalID) x ' +
'ORDER BY ' + @sortOrder + ') x ' +
'ORDER BY ' + @reverseSortOrder + ') x ' +
'ORDER BY ' + @sortOrder
exec sp_executesql @sql,
N'@portalID int',
@portalID = @portalID
END