Paging sort stored procedure error, help

redfish119

VerseNumber int 4
SubmitTime datetime 8
VerseText nvarchar 2000
UserID nvarchar 50
This is the table structure
I can't run the stored procedure directly in sql

fanfan1980

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

In ConstructionVerseLog:
VerseNumber int 4

-An error occurred during these two conversions

fanfan1980

--try

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

declare @sql varchar (8000)
---- Create a table variable with an identifier column
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)
)
'
---- Stop processing the query after returning the specified number of @StopRow lines
Set RowCount @StopRow

---- Insert into table variable
set @ sql = @ sql +
'
 insert @t_table
(
[VerseNumber], [SubmitTime], [VerseText], [UserID])
SELECT rtrim ([VerseNumber]), [SubmitTime], [VerseText], [UserID]
FROM ConstructionVerseLog
order by ['+ @ SortColumn +'] '

---- Return to the correct result
set @ sql = @ sql +
'SELECT * FROM @t_table WHERE rownum> =' + @StartRow +
'ORDER BY rownum'
EXEC (@sql)
Set RowCount 0-it is better to add this sentence

/ * 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

Using Microsoft's stored procedures, achieved, thank youfanfan1980

neo19831001

There are limits to writing the paging stored procedure like this, and it can be written more general