分页排序存储过程出错，求助

redfish119

ALTER PROCEDURE dbo.VerseLogPaging
       
@SortColumn as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
AS

---- 建立有标识符列的table变量
declare @t_table table
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[SortColumn] [varchar] (40) ,
[VerseNumber] [varchar] (20) ,
[SubmitTime] [datetime] (20) ,
[VerseText] [varchar] (500) ,
[UserID] [varchar] (20)
)

---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow

---- 插入到table变量中
insert @t_table
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT [VerseNumber],[SubmitTime],[VerseText],[UserID]

FROM ConstructionVerseLog
order by @SortColumn


---- 返回到正确的结果
SELECT * FROM @t_table WHERE rownum >= @StartRow
ORDER BY rownum

        /* SET NOCOUNT ON */
        RETURN

错误：
1：[SubmitTime] [datetime] (20)  ,声明datetime类型时出错
2：order by @SortColumn ,使用传递参数时出错

请问要如何修改啊

我是杀人狂

declare @t_table table
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[SortColumn] [varchar] (40) ,
[VerseNumber] [varchar] (20) ,
[SubmitTime] [datetime] ,  不用加后面的
[VerseText] [varchar] (500) ,
[UserID] [varchar] (20)
)

我是杀人狂

order by SortColumn

kittast

1.
[SubmitTime] [datetime] (20) ,
改为
[SubmitTime] [datetime]  ,

2.order by @SortColumn
要使用动态SQL

fanfan1980

1：[SubmitTime] [datetime] (20)  ,声明datetime类型时出错

[SubmitTime] [datetime], --datetime不用指定長度

hiqwolfzzh

把datetime (20)的(20)去掉

redfish119

排序如何实现

fanfan1980

--try

ALTER PROCEDURE dbo.VerseLogPaging
       
@SortColumn as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
AS

declare @sql varchar(8000)
---- 建立有标识符列的table变量
set @sql='
declare @t_table table
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[SortColumn] [varchar] (40) ,
[VerseNumber] [varchar] (20) ,
[SubmitTime] [datetime],
[VerseText] [varchar] (500) ,
[UserID] [varchar] (20)
)
'
---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow

---- 插入到table变量中
set @sql=@sql+
'
insert @t_table
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT [VerseNumber],[SubmitTime],[VerseText],[UserID]
FROM ConstructionVerseLog
order by ['+@SortColumn+'] '

---- 返回到正确的结果
set @sql=@sql+
' SELECT * FROM @t_table WHERE rownum >='+ @StartRow +
' ORDER BY rownum '
EXEC(@sql)
Set RowCount 0 --最好加上這句

        /* SET NOCOUNT ON */
        RETURN

redfish119

楼上的
order by ['+@SortColumn+'] 还是出错
这样传递貌似没区别

fanfan1980

你把@sql打印出來看看