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:        <author,,name>
-- create date: <create date,,>
-- description:    <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

补充：asp.net教程,数据库应用