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

cv4bf5d65fd

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] ,
[VerseText] [varchar] (500) ,
[UserID] [varchar] (20)
)
declare @sql varchar(8000)

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

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

---- 返回到正确的结果
set @sql = 'SELECT * FROM '+@t_table+' WHERE rownum >= '+@StartRow+' ORDER BY rownum'
exec(@sql)

/* SET NOCOUNT ON */
RETURN

redfish119

未声明@t_table

fanfan1980

order by ['+@SortColumn+'] '

改為

order by '+@SortColumn+' '

去掉[]試試

redfish119

- -还是不行

急啊

fanfan1980

print @sql

--把@sql打印出來看看

redfish119

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

Syntax error converting the varchar value ' ORDER BY rownum ' to a column of data type int.
(20 row(s) affected)
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[VerseLogPaging].
The thread '(null)(56)' (0x800003cc) has exited with code 0 (0x0).
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).

redfish119

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 --最好加上這句

fanfan1980

Syntax error converting the varchar value ' ORDER BY rownum ' to a column of data type int.

--數據類型轉換出錯

redfish119

SELECT * FROM @t_table WHERE rownum >='+ @StartRow +

@StartRow声明的是int型啊

fanfan1980

把你調用存儲過程的SQL語句和 ConstructionVerseLog 的表結構貼出來看看