以下是我的存储过程大概,其中Days是用两个表的CreateDate时间计算出来的天数,现在要用来排序,查询出来的数据会有3,5,7,1,null这类的。现在得出的排序效果类似:null,null,1,3,null,5,7,null,需求是要这样:null,null,null,null,1,3,5,7.
求解决方法,跪谢~
CREATE PROCEDURE SP_PROC_NAME(
@SortExpression VARCHAR(50) = ' Days ASC '
)
AS
BEGIN
SET @SortExpression=CASE WHEN CHARINDEX('Days',@SortExpression)>0 THEN REPLACE( @SortExpression,'Days','DATEDIFF(DAY,e.CreatedDate,b.CreatedDate)') ELSE 'b.'+RTRIM(LTRIM(@SortExpression)) END
DECLARE @strsql NVARCHAR(MAX)= N'';
SELECT @strsql = N' SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortExpression
+ N') AS rowNum,
CASE WHEN b.TypeID=2 THEN DATEDIFF(DAY,e.CreatedDate,b.CreatedDate)+1 ELSE null END AS Days
FROM b INNER JOIN e ON ......'
EXEC(@strsql)
END