【www.gdgbn.com--php常用代码】

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

本文来源：http://www.gdgbn.com/jiaocheng/28958/