asp.net sql存储过程分页代码

use [data_smf]
go
/****** 对象:  storedprocedure [dbo].[catsearch]    脚本日期: 01/23/2011 04:34:30 ******/
set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- author:       
-- create date:
-- description:   
-- =============================================
create procedure [dbo].[catsearch]

@perfectkey   nvarchar(20)='close',--是否完全匹配
@pagecout     int=1, --返回总页数或数据 等于0时 返回总页数 等于 -1时返回第一页和总页数
@searchvalue  nvarchar(50)='-',--要查找的内容
@searchcolumn nvarchar(50)='-',--查询的列名
@country      nvarchar(50)='-',--地域

@numberfew    int=-1,
@numbermany   int=-1,
@pricelow     int=-1,
@pricehigh    int=-1,
@days         int=3,

@ordercolmn   nvarchar(50)='puttime',--用于排序的字段
@order        nvarchar(50)='desc',
@size         int=20, --每页显示数据的条数
@column       nvarchar(100)='*',-- 返回的字段
@table1        nvarchar(100)='produce',--要查询的表名称
@table2       nvarchar(100)='produce4',--要查询的表名称
@table3        nvarchar(100)='-'--要查询的表名称
as
begin
    set nocount on; 
declare  @shortcache  nvarchar(1000)
declare  @selectstr   nvarchar(1000)
declare  @sqlpagecountstr nvarchar(3000)
declare  @sqlstr      nvarchar(3000)
declare  @sqlstr1     nvarchar(1000)
declare  @sqlstr2     nvarchar(1000)
declare  @sqlstr3     nvarchar(1000)
declare  @ifnull      int
set @ifnull=0
set  @selectstr=' where '
set  @shortcache=' '

 

-------------数量 [count] 为用于排序的列名
-------------用于对商品数量的查询 产生一个由and 连接的条件 当不输入条件时跳过
----完成
if(@numberfew<>-1 and @numbermany<>-1)
begin
set @shortcache=' ([counts] between '+cast(@numberfew as nvarchar(50))+' and '+cast(@numbermany as nvarchar(50))+' )  and '
end
 else if(@numberfew<>-1 and @numbermany=-1)
begin
set @shortcache=' ([counts]=>'+cast(@numberfew as nvarchar(50))+') and '
end
 else if(@numberfew=-1 and @numbermany<>-1)
begin
set @shortcache=' ([counts]<='+cast(@numbermany as nvarchar(50))+') and '
end
if(@shortcache<>' ')
begin
set @selectstr=@selectstr+@shortcache
set  @shortcache=' '
end
else
set @ifnull=@ifnull+1

----价格 [price]price 为用于排序的列名
----用于限制商品价格由and 连接 当不输入值时跳过
---- 完成
if(@pricelow<>-1 and @pricehigh<>-1)
begin
set @shortcache=' ([price] between '+cast(@pricelow as nvarchar(50))+' and '+cast(@pricehigh as nvarchar(50))+ ') and '
end
if(@pricelow<>-1 and @pricehigh=-1)
begin
set @shortcache='  (cast([price] as int)>'+cast(@pricelow as nvarchar(50))+ ') and '
end
if(@pricelow=-1 and @pricehigh<>-1)
begin
set @shortcache='  (cast([price] as int)<'+cast(@pricehigh as nvarchar(50))+ ') and '
end
if(@shortcache<>' ')
begin
set @selectstr=@selectstr+@shortcache
set  @shortcache=' '
end
else
set @ifnull=@ifnull+1

--------时间 [puttime]为用于排序的列名
if(@days<>-1)
begin
set @shortcache='  (cast(datediff(dd,[puttime],getdate()) as int ) between -1  and '+convert(nvarchar(50),@days)+' )and '
set @selectstr=@selectstr+@shortcache
end
else
set @ifnull=@ifnull+1

--------地址
if(@country<>'-')
begin
set @shortcache= ' (country='''+@country+''') and '
set @selectstr=@selectstr+@shortcache
end
else
set @ifnull=@ifnull+1

------完全匹配 已经检测完成 [name] [text][trader]为用于排序的列名
if(@perfectkey='open' and @searchvalue <>'-') 
begin
if(@searchcolumn='-')
begin
set  @shortcache=' ([name]='''+@searchvalue+''' or '+' [text]='''+@searchvalue+''' or '+' [trader]='''+@searchvalue+''') and '
----------set  @shortcache=' where [name]=''100'' or [text]=''100'' or [price]=''100'''
set @selectstr=@selectstr+@shortcache
end
else
begin
set @shortcache=' (['+@searchcolumn+']='''+@searchvalue+''') and  '
----------set  @shortcache=' where [name]=''土豆'''
set @selectstr=@selectstr+@shortcache
end
end

 

--------不完全匹配 已经检测完成
 else if (@perfectkey='close' and @searchvalue <>'-')
begin
if (@searchcolumn='-')
begin
set @shortcache=' ([name] like''%'+@searchvalue+'%'' or  [text] like''%'+@searchvalue+'%'' or [trader] like''%'+@searchvalue+'%'') and '
----------set  @shortcache='1=1'
set @selectstr=@selectstr+@shortcache
end
else
begin
set @shortcache=' ('+@searchcolumn+' like''%'+@searchvalue+'%'') and '
set @selectstr=@selectstr+@shortcache
end
end
else
begin
set @ifnull=@ifnull+1
end
if(@ifnull=5)
begin
set @selectstr= ' '
end
else
begin
set @selectstr=@selectstr+' 1=1 '
end

---------合并查询语句
set @sqlstr1 = ' select top 200  '+@column+'  from  '+@table1+' '+@selectstr+'  '
if(@table2<>'-')
begin
set @sqlstr2 = ' select top 200  '+@column+' from  '+@table2+ '  '+@selectstr+'  '
end
else
begin
set @sqlstr2='  '
end

if(@table3<>'-')
begin
set @sqlstr3 = ' select top 200  '+@column+'  from  '+@table3+ ' '+@selectstr+'  '
end
else
begin
set @sqlstr3='  '
end
set @sqlstr =' select * from ( select top 1000 row_number() over( order by '+@ordercolmn+' '+@order+ ' ) as rownum ,*from ('+@sqlstr1+' union all '+@sqlstr2+') s ) t where t.rownum between '+cast(@size as nvarchar(20))+'*('+cast(@pagecout as nvarchar(20))+'-1 ) and '+cast(@size as nvarchar(20))+'*'+cast(@pagecout as nvarchar(20))+' '
set @sqlpagecountstr=' select rownumber=ceiling(count(id) *1.0/'+cast(@size as nvarchar(20))+' from ( select top 1000 row_number() over( order by '+@ordercolmn+' ) as rownum  from ('+@sqlstr1+' union all '+@sqlstr2+') s ) t'

 

----返回总行数
if(@pagecout=0)
begin
exec(@sqlpagecountstr)
end
------返回调用页的数据
 if(@pagecout>0)
begin
  exec(@sqlstr)
end
------返回总页数，并且返回第一页数据
if(@pagecout=-1)
begin
exec(@sqlstr)
set @pagecout=1
set @sqlpagecountstr=' select rownumber=ceiling(count(id) *1.0/'+cast(@size as nvarchar(20))+' from ( select top 1000 row_number() over( order by '+@ordercolmn+' ) as rownum  from ('+@sqlstr1+' union all '+@sqlstr2+') s ) t'
exec(@sqlpagecountstr)
end
end