| |

VerySource

 Forgot password?
 Register
Search
View: 24|Reply: 24

Paging sort stored procedure error, help

[Copy link]

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

Post time: 2020-3-3 03:00:01
| Show all posts |Read mode
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?
Reply

Use magic Report

0

Threads

99

Posts

58

Credits

Newbie

Rank: 1

Credits
58

 China

Post time: 2020-5-14 15:00:01
| Show all posts
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)
)
Reply

Use magic Report

0

Threads

99

Posts

58

Credits

Newbie

Rank: 1

Credits
58

 China

Post time: 2020-5-14 18:15:01
| Show all posts
order by SortColumn
Reply

Use magic Report

2

Threads

18

Posts

16

Credits

Newbie

Rank: 1

Credits
16

 China

Post time: 2020-5-14 22:45:01
| Show all posts
1.
[SubmitTime] [datetime] (20),
To
[SubmitTime] [datetime],

2.order by @SortColumn
To use dynamic SQL
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-5-14 23:00:01
| Show all posts
1: [SubmitTime] [datetime] (20), error when declaring datetime type

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

Use magic Report

0

Threads

36

Posts

26

Credits

Newbie

Rank: 1

Credits
26

 China

Post time: 2020-5-15 17:45:01
| Show all posts
Remove (20) from datetime (20)
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-5-16 22:15:01
| Show all posts
How to sort
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-5-17 11:30:02
| Show all posts
--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
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-5-19 13:30:01
| Show all posts
upstairs
order by ['+ @ SortColumn +'] is still wrong
This seems to make no difference
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-5-19 16:00:01
| Show all posts
You print out @sql and see
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list