ALTER PROCEDURE dbo.VerseLogPaging
@SortColumn as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
AS---- 建立有标识符列的table变量
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)
)---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow---- 插入到table变量中
insert @t_table
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT [VerseNumber],[SubmitTime],[VerseText],[UserID]FROM ConstructionVerseLog
order by @SortColumn
---- 返回到正确的结果
SELECT * FROM @t_table WHERE rownum >= @StartRow
ORDER BY rownum /* SET NOCOUNT ON */
RETURN错误:
1:[SubmitTime] [datetime] (20) ,声明datetime类型时出错
2:order by @SortColumn ,使用传递参数时出错请问要如何修改啊
@SortColumn as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
AS---- 建立有标识符列的table变量
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)
)---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow---- 插入到table变量中
insert @t_table
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT [VerseNumber],[SubmitTime],[VerseText],[UserID]FROM ConstructionVerseLog
order by @SortColumn
---- 返回到正确的结果
SELECT * FROM @t_table WHERE rownum >= @StartRow
ORDER BY rownum /* SET NOCOUNT ON */
RETURN错误:
1:[SubmitTime] [datetime] (20) ,声明datetime类型时出错
2:order by @SortColumn ,使用传递参数时出错请问要如何修改啊
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[SortColumn] [varchar] (40) ,
[VerseNumber] [varchar] (20) ,
[SubmitTime] [datetime] , 不用加后面的
[VerseText] [varchar] (500) ,
[UserID] [varchar] (20)
)
[SubmitTime] [datetime] (20) ,
改为
[SubmitTime] [datetime] ,2.order by @SortColumn
要使用动态SQL
@SortColumn as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
ASdeclare @sql varchar(8000)
---- 建立有标识符列的table变量
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)
)
'
---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow---- 插入到table变量中
set @sql=@sql+
'
insert @t_table
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT [VerseNumber],[SubmitTime],[VerseText],[UserID]
FROM ConstructionVerseLog
order by ['+@SortColumn+'] '---- 返回到正确的结果
set @sql=@sql+
' SELECT * FROM @t_table WHERE rownum >='+ @StartRow +
' ORDER BY rownum '
EXEC(@sql)
Set RowCount 0 --最好加上這句 /* SET NOCOUNT ON */
RETURN
order by ['+@SortColumn+'] 还是出错
这样传递貌似没区别
@SortColumn as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = nullAS---- 建立有标识符列的table变量
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)---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow---- 插入到table变量中
set @sql = 'insert '+@t_table+' ([VerseNumber],[SubmitTime],[VerseText],[UserID]) SELECT [VerseNumber],[SubmitTime],[VerseText],[UserID] FROM ConstructionVerseLog order by '+@SortColumn
exec(@sql)---- 返回到正确的结果
set @sql = 'SELECT * FROM '+@t_table+' WHERE rownum >= '+@StartRow+' ORDER BY rownum'
exec(@sql)/* SET NOCOUNT ON */
RETURN
(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).
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT [VerseNumber],[SubmitTime],[VerseText],[UserID]
FROM ConstructionVerseLog
order by '+@SortColumn+'---- 返回到正确的结果
set @sql=@sql+
' SELECT * FROM @t_table WHERE rownum >='+ @StartRow +
' ORDER BY rownum '
EXEC(@sql)
Set RowCount 0 --最好加上這句
SubmitTime datetime 8
VerseText nvarchar 2000
UserID nvarchar 50
这是表结构
我直接在sql里运行存储过程就不行
[VerseNumber] [varchar] (20) ConstructionVerseLog 中:
VerseNumber int 4--這兩個轉換時出錯
@SortColumn as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
ASdeclare @sql varchar(8000)
---- 建立有标识符列的table变量
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)
)
'
---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow---- 插入到table变量中
set @sql=@sql+
'
insert @t_table
(
[VerseNumber],[SubmitTime],[VerseText],[UserID])
SELECT rtrim([VerseNumber]),[SubmitTime],[VerseText],[UserID]
FROM ConstructionVerseLog
order by ['+@SortColumn+'] '---- 返回到正确的结果
set @sql=@sql+
' SELECT * FROM @t_table WHERE rownum >='+ @StartRow +
' ORDER BY rownum '
EXEC(@sql)
Set RowCount 0 --最好加上這句 /* SET NOCOUNT ON */
RETURN
(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).
无为搜索引擎开始测试了啊
(第三次开放测试啊) 网站运行后的效果请参见:
http://219.233.38.213/Search/WuWei.aspx
代码下载地址 :http://www.ofile.cn/se.rar
前两次用 CGI+html做了一个版本还做了一个 Java版本 (这次比较忙,所以没来的及更新,有需要的跟我说一声啊)
这次用 C# ASP.NET做一个 玩玩
做的不好,请大家做指教啊!
这次还实现了计算器功能啊
可以搜索一下 sin(1+2) 看看
另外 sh=1&sf=1&ua=1&sa=1&st=1&wh=1&ph=1 把这里面的 1改成 0看看 每个部分都是可以控制的:)
至于为什么要把客户端网站部分开源,请参考我第二次开放测试时即兴写的文章 : 最近我研究搜索引擎 七 (长长中国人的志气篇)
http://www.baidu.com/s?wd=%D7%EE%BD%FC%CE%D2%D1%D0%BE%BF%CB%D1%CB%F7%D2%FD%C7%E6%C6%DF&cl=3
承盟广大网友看的起 该文目前已经被全国 2000多网站转载
先说一下技术指标: 本次我收录了全国30多万个一、二级域名, 网页采集用了三周时间,目前已经采集了 12万网站,平均每个网站收录 30篇网页,索引进行了 10天,
目前已经索引了 100多万网页 。 搜索核心部分采用这次采用 C 语言开发。 客户端提供三个版本一个CGI+HTML版本,一个 Java版本,一个 ASP.NET版本。代码下载地址 :http://www.ofile.cn/se.rar
搜索网站客户端(.NET版本)整体解决方案(源代码)
(第三次开放测试啊) 网站运行后的效果请参见:
http://219.233.38.213/Search/WuWei.aspx
http://fnsword.com/bak/Pagination.rarPagination.sql 分页T-SQL
Dept.sql 产出测试表
sql_page.sql 分页测试