按行读取
CREATE PROCEDURE GetDataByLine
(
--创建一个分页读取过程
@SqlStr varchar(8000), --SQL语句
@FirstRec int, --页起始行
@LastRec int --页结束行
)
AS
DECLARE @dt varchar(10) --生成临时表的随机数
BEGIN
SELECT @dt= substring(convert(varchar, rand()), 3, 10) --一个字符型的随机数
--将搜索结果放入临时表中，表名随机生成，在' FROM '前插入'INTO '+随机临时表名
SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ', upper(@SqlStr)), 6 ,' INTO tempdb..Lining' + @dt + ' FROM ')
EXECUTE (@SqlStr)
--为临时表增加id号
SELECT @SqlStr = 'ALTER TABLE tempdb..Lining' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)
--计算临时表中的记录数
--SELECT @SqlStr = 'SELECT Count(*) From tempdb..Lining' + @dt
--EXECUTE (@SqlStr)
--选取记录号在起始行和结束行中间的记录
SELECT @SqlStr = 'SELECT * FROM tempdb..Lining' + @dt + ' WHERE TEMPDB_ID > ' + convert(varchar, @FirstRec) + ' and TEMPDB_ID <= ' + convert(varchar, @LastRec)
EXECUTE (@SqlStr)
--删除临时表
SELECT @SqlStr = 'DROP TABLE tempdb..Lining'+@dt
EXECUTE (@SqlStr)
END
/*
some comments:
1.@SqlStr varchar(8000), depends on your page size
2. this is a generic paging sp, if you just want to use it for specific table,
you'd better change the 'tempdb..Lining' to #Paging, the performance will be better
*/
按页读取
CREATE PROCEDURE GetDataByPage
(
--创建一个分页读取过程
@SqlStr varchar(8000), --SQL语句
@PageSize int, --每页记录数
@CurrentPage int --当前页数
)
AS
DECLARE @FirstRec int, @LastRec int, @dt varchar(10) --页起始行，页结束行，生成临时表的随机数
BEGIN
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize --计算页起始行
SELECT @LastRec = (@CurrentPage * @PageSize + 1) --计算页结束行
SELECT @dt= substring(convert(varchar,rand()),3,10) --一个字符型的随机数
--将搜索结果放入临时表中，表名随机生成，在' FROM '前插入'INTO '+随机临时表名
SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ',upper(@SqlStr)), 6 ,' INTO tempdb..Paging'+@dt+' FROM ')
EXECUTE (@SqlStr)
--为临时表增加id号
SELECT @SqlStr = 'ALTER TABLE tempdb..Paging'+@dt+' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)
--计算临时表中的记录数
--SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
--EXECUTE (@SqlStr)
--选取记录号在起始行和结束行中间的记录
SELECT @SqlStr = 'SELECT * FROM tempdb..Paging' + @dt + ' WHERE TEMPDB_ID > ' + convert(varchar,@FirstRec) + ' and TEMPDB_ID < '+convert(varchar,@LastRec)
EXECUTE (@SqlStr)
--删除临时表
SELECT @SqlStr = 'DROP TABLE tempdb..Paging'+@dt
EXECUTE (@SqlStr)
END
/*
some comments:
1. @SqlStr varchar(8000), depends on your page size
2. this is a generic paging sp, if you just want to use it for specific table,
you'd better change the 'tempdb..Paging' to #Paging, the performance will be better
*/
create procedure splitpage @qry varchar(16384),@ipage int, @num int as /*@qry SQL语句, @ipage 页数, @num 每页记录条数 */
begin
declare @maxpages int
declare @rcount int
declare @execsql varchar(16384)
if @ipage>=100
select @maxpages=ceiling(count(*)/@num) from test
else
select @maxpages=100000
if @ipage<=@maxpages/2
begin
select @rcount=@ipage*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
execute (@execsql)
end else
begin
select @rcount=(@maxpages-@ipage+1)*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' order by sybid desc'
set @execsql = @execsql || ' select * from #temptable1 where sybid > ' || convert(varchar,(@maxpages-@ipage)*@num) || ' and sybid <= ' || convert(varchar,(@maxpages-@ipage+1)*@num)
execute (@execsql)
end
end
select stuff(stuff(convert(char(10),getdate(),111),5,1,'-'),8,1,'-')as op_time
SELECT DATEFORMAT( '1989-01-01 01:02:03', 'hh:mm:ss' );会报错
getdate()
得到当前时间,可以设置得到各种时间格式.
datepart(日期部分,日期)
取指定时间的某一个部分,年月天时分秒.
datediff(日期部分,日期1,日期2)
计算指定的日期1和日期2的时间差多少.
dateadd(日期部分,数值表达式,日期)
计算指定时间,再加上表达式指定的时间长度.
--取时间的某一个部分
select datepart(yy,getdate()) --year
select datepart(mm,getdate()) --month
select datepart(dd,getdate()) --day
select datepart(hh,getdate()) --hour
select datepart(mi,getdate()) --min
select datepart(ss,getdate()) --sec
--取星期几
set datefirst 1
select datepart(weekday,getdate()) --weekday
--字符串时间
select getdate() -- '03/11/12'
select convert(char,getdate(),101) -- '09/27/2003'
select convert(char,getdate(),102) -- '2003.11.12'
select convert(char,getdate(),103) -- '27/09/2003'
select convert(char,getdate(),104) -- '27.09.2003'
select convert(char,getdate(),105) -- '27-09-2003'
select convert(char,getdate(),106) -- '27 Sep 2003'
select convert(char,getdate(),107) --'Sep 27, 2003'
select convert(char,getdate(),108) --'11:16:06'
select convert(char,getdate(),109) --'Sep 27 2003 11:16:28:746AM'
select convert(char,getdate(),110) --'09-27-2003'
select convert(char,getdate(),111) --'2003/09/27'
select convert(char,getdate(),112) --'20030927'
select rtrim(convert(char,getdate(),102))+' '+(convert(char,getdate(),108)) -- '2003.11.12 11:03:41'
--整数时间
select convert(int,convert(char(10),getdate(),112)) -- 20031112
select datepart(hh,getdate())*10000 + datepart(mi,getdate())*100 + datepart(ss,getdate()) -- 110646
--时间格式 "YYYY.MM.DD HH:MI:SS" 转换为 "YYYYMMDDHHMISS"
declare @a datetime,@tmp varchar(20),@tmp1 varchar(20)
select @a=convert(datetime,'2004.08.03 12:12:12')
select @tmp=convert(char(10),@a,112)
select @tmp
select @tmp1=convert(char(10),datepart(hh,@a)*10000 + datepart(mi,@a)*100 + datepart(ss,@a))
select @tmp1
select @tmp=@tmp+@tmp1
select @tmp
