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
本人新手 刚写的多表分页查询
有不足之处 和改进之处 请指点
