ALTER PROCEDURE up_get_links_by_catid @CatID INT, @SortDirection VARCHAR(15), @SortExpression VARCHAR(20) AS BEGIN DECLARE @ParentCatID int DECLARE @L VARCHAR(5) SELECT @ParentCatID=ParentCatID FROM dbo.Categories_Link WHERE CatID=@CatID IF @ParentCatID=0 BEGIN DECLARE @SQL_P VARCHAR(500) DECLARE @CatID_P INT SET @CatID_P=@CatID SET @SQL_P='SELECT L.*,dbo.GetUserFavoritesCountByLid(L.LID) AS UserFavoritesCount,dbo.GetReviewsCountByCid(L.LID) AS ReviewsCount,CASE ISNULL(U.UserName,''-1'') WHEN ''-1'' THEN ''游客'' ELSE U.UserName END AS UserName,C.* FROM Links AS L LEFT OUTER JOIN Users AS U ON L.UID=U.UID LEFT OUTER JOIN Categories_Link AS C ON L.CatID=C.CatID WHERE L.CatID IN(SELECT CatID FROM Categories_Link WHERE ParentCatID='+ STR(@CatID_P) +' ) ORDER BY' IF @SortExpression='UserFavoritesCount' OR @SortExpression='ReviewsCount' BEGIN SET @L=' ' END ELSE BEGIN SET @L=' L.' END SET @SQL_P=@SQL_P+@L+@SortExpression+' '+@SortDirection EXEC(@SQL_P) END ELSE BEGIN DECLARE @SQL_S VARCHAR(500) DECLARE @CatID_S INT SET @CatID_S=@CatID SET @SQL_S='SELECT L.*,dbo.GetUserFavoritesCountByLid(L.LID) AS UserFavoritesCount,dbo.GetReviewsCountByCid(L.LID) AS ReviewsCount,CASE ISNULL(U.UserName,''-1'') WHEN ''-1'' THEN ''游客'' ELSE U.UserName END AS UserName,C.* FROM Links AS L LEFT OUTER JOIN Users AS U ON L.UID=U.UID LEFT OUTER JOIN Categories_Link AS C ON L.CatID=C.CatID WHERE L.CatID='+ STR(@CatID_S) +' ORDER BY' IF @SortExpression='UserFavoritesCount' OR @SortExpression='ReviewsCount' BEGIN SET @L=' ' END ELSE BEGIN SET @L=' L.' END SET @SQL_S=@SQL_S+@L+@SortExpression+' '+@SortDirection EXEC(@SQL_S) END END 为什么那两个标量函数加上就会出现错误?
@CatID INT,
@SortDirection VARCHAR(15),
@SortExpression VARCHAR(20)
AS
BEGIN
DECLARE @ParentCatID int
DECLARE @L VARCHAR(5)
SELECT @ParentCatID=ParentCatID FROM dbo.Categories_Link WHERE CatID=@CatID
IF @ParentCatID=0
BEGIN
DECLARE @SQL_P VARCHAR(500)
DECLARE @CatID_P INT
SET @CatID_P=@CatID
SET @SQL_P='SELECT L.*,dbo.GetUserFavoritesCountByLid(L.LID) AS UserFavoritesCount,dbo.GetReviewsCountByCid(L.LID) AS ReviewsCount,CASE ISNULL(U.UserName,''-1'') WHEN ''-1'' THEN ''游客'' ELSE U.UserName END AS UserName,C.*
FROM Links AS L LEFT OUTER JOIN Users AS U ON L.UID=U.UID
LEFT OUTER JOIN Categories_Link AS C ON L.CatID=C.CatID
WHERE L.CatID IN(SELECT CatID FROM Categories_Link WHERE ParentCatID='+ STR(@CatID_P) +' )
ORDER BY'
IF @SortExpression='UserFavoritesCount' OR @SortExpression='ReviewsCount'
BEGIN
SET @L=' '
END
ELSE
BEGIN
SET @L=' L.'
END
SET @SQL_P=@SQL_P+@L+@SortExpression+' '+@SortDirection
EXEC(@SQL_P)
END
ELSE
BEGIN
DECLARE @SQL_S VARCHAR(500)
DECLARE @CatID_S INT
SET @CatID_S=@CatID
SET @SQL_S='SELECT L.*,dbo.GetUserFavoritesCountByLid(L.LID) AS UserFavoritesCount,dbo.GetReviewsCountByCid(L.LID) AS ReviewsCount,CASE ISNULL(U.UserName,''-1'') WHEN ''-1'' THEN ''游客'' ELSE U.UserName END AS UserName,C.*
FROM Links AS L LEFT OUTER JOIN Users AS U ON L.UID=U.UID
LEFT OUTER JOIN Categories_Link AS C ON L.CatID=C.CatID
WHERE L.CatID='+ STR(@CatID_S) +'
ORDER BY'
IF @SortExpression='UserFavoritesCount' OR @SortExpression='ReviewsCount'
BEGIN
SET @L=' '
END
ELSE
BEGIN
SET @L=' L.'
END
SET @SQL_S=@SQL_S+@L+@SortExpression+' '+@SortDirection
EXEC(@SQL_S)
END
END
为什么那两个标量函数加上就会出现错误?