| |

VerySource

 Forgot password?
 Register
Search
Author: redfish119

Paging sort stored procedure error, help

[Copy link]

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-6-1 11:15:01
| Show all posts
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
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-6-1 21:30:01
| Show all posts
@t_table:
[VerseNumber] [varchar] (20)

In ConstructionVerseLog:
VerseNumber int 4

-An error occurred during these two conversions
Reply

Use magic Report

0

Threads

205

Posts

104

Credits

Newbie

Rank: 1

Credits
104

 China

Post time: 2020-6-1 22:30:01
| 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 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
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-6-3 01:30:02
| Show all posts
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).
Reply

Use magic Report

1

Threads

17

Posts

15

Credits

Newbie

Rank: 1

Credits
15

 Hong Kong

 Author| Post time: 2020-6-8 17:45:02
| Show all posts
Using Microsoft's stored procedures, achieved, thank youfanfan1980
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