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

redfish119

VerseNumber        int        4
SubmitTime        datetime        8
VerseText                nvarchar        2000
UserID                nvarchar        50
这是表结构
我直接在sql里运行存储过程就不行

fanfan1980

@t_table 中:
[VerseNumber] [varchar] (20)

ConstructionVerseLog 中:
VerseNumber        int        4

--這兩個轉換時出錯

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 rtrim([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

No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[VerseLogPaging] ( @SortColumn = versenumber, @StartRow = 6, @StopRow = 20 ).

Syntax error converting the varchar value '
declare @t_table table
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[SortColumn] [varchar] (40) ,
[VerseNumber] [int],
[SubmitTime] [datetime],
[VerseText] [varchar] (500) ,
[UserID] [varchar] (20)
)

insert @t_table
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT ([VerseNumber]),[SubmitTime],[VerseText],[UserID]....
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[VerseLogPaging].
The thread '(null)(59)' (0x800003cc) has exited with code 0 (0x0).
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).

redfish119

用微软的存储过程，实现了，谢谢fanfan1980

neo19831001

分页存储过程这样写还是有极限性，可以写的更通用一些