Paging sort stored procedure error, help

redfish119

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

---- Create table variable with identifier column
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)
)

---- Stop processing the query after returning the specified number of @StopRow rows
Set RowCount @StopRow

---- Inserted into the table variable
insert @t_table
(
[VerseNumber], [SubmitTime], [VerseText], [UserID])
SELECT [VerseNumber], [SubmitTime], [VerseText], [UserID]

FROM ConstructionVerseLog
order by @SortColumn


---- return to the correct result
SELECT * FROM @t_table WHERE rownum> = @StartRow
ORDER BY rownum

/ * SET NOCOUNT ON * /
RETURN

error:
1: [SubmitTime] [datetime] (20), error when declaring datetime type
2: order by @SortColumn, error when using pass parameters

How do I change it?

我是杀人狂

declare @t_table table
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL,
[SortColumn] [varchar] (40),
[VerseNumber] [varchar] (20),
[SubmitTime] [datetime], without adding the following
[VerseText] [varchar] (500),
[UserID] [varchar] (20)
)

我是杀人狂

order by SortColumn

kittast

1.
[SubmitTime] [datetime] (20),
To
[SubmitTime] [datetime],

2.order by @SortColumn
To use dynamic SQL

fanfan1980

1: [SubmitTime] [datetime] (20), error when declaring datetime type

[SubmitTime] [datetime], --datetime does not need to specify the length

hiqwolfzzh

Remove (20) from datetime (20)

redfish119

How to sort

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

upstairs
order by ['+ @ SortColumn +'] is still wrong
This seems to make no difference

fanfan1980

You print out @sql and see