| |

VerySource

 Forgot password?
 Register
Search
Author: redfish119

Paging sort stored procedure error, help

[Copy link]

0

Threads

74

Posts

47

Credits

Newbie

Rank: 1

Credits
47

 China

Post time: 2020-5-19 21:45:01
| Show all posts
ALTER PROCEDURE dbo.VerseLogPaging
The
@SortColumn as varchar (100) = null,
@StartRow as int = null,
@StopRow as int = null

AS

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

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

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

---- Return to the correct result
set @sql = 'SELECT * FROM' + @ t_table + 'WHERE rownum> =' + @ StartRow + 'ORDER BY rownum'
exec (@sql)

/ * 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-20 17:30:01
| Show all posts
Undeclared @t_table
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-5-21 10:15:01
| Show all posts
order by ['+ @ SortColumn +'] '

To

order by '+ @ SortColumn +' '

Remove [] try
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-5-21 20:45:01
| Show all posts
- -Or not

Anxious
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-5-22 09:00:02
| Show all posts
print @sql

--Print @sql and see
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-5-23 11:15:02
| Show all posts
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).
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Japan

 Author| Post time: 2020-5-23 11:45:01
| Show all posts
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
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-5-28 01:30:01
| Show all posts
Syntax error converting the varchar value 'ORDER BY rownum' to a column of data type int.

--Data type conversion error
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-5-30 09:30:01
| Show all posts
SELECT * FROM @t_table WHERE rownum> = '+ @StartRow +

@StartRow declares an int type
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-5-30 21:45:01
| Show all posts
Paste the SQL statement of the stored procedure and the table structure of ConstructionVerseLog to 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