字段检索方案

declare @day int

declare @weekday int

declare @koul dec(14,2) ---是否会员享受 是、否

declare @rq char(19)

set @day =0

set @weekday = 0

set @koul= 0

select @day = datepart(day,getdate())

select @weekday = case when datepart(weekday,getdate())-1 =0 then 7 else datepart(weekday,getdate())-1 end

----当星期天返回7

----检查当天是否有促销

select top 1 @koul= koul from cardrate where :mast.rq + :mast.ontime between srq+stime and erq+etime

and (yhweek=0 or yhweek=@weekday) and (yhday=0 or yhday=@day) and (cardid='ALL' or cardid=:value)

order by koul desc

select top 200 b.xingming as KKHNAME,a.kkhcode,a.isdj,a.cardid,a.gukeid,a.askpass,a.mima,a.is_shfzh,a.sxrq,

case when (@koul < 0 ) then 100.00

when (@koul = 0 ) then a.koul

when @koulelse a.koul end koul ,a.touzxe,a.ye,a.jifen

from ret_cuxiaoka a ,ret_gukezl b

where a.beactive='是' and (a.kkhcode like '%'+ :value +'%' or b.shouji like '%'+ :value +'%' ) and a.gukeid=b.gukeid

;

pihao>> select pihao2,pihao,shl-xsshl as hwshl from sphwph where spid=:spid and hw=:mast.hw and shl>0 order by shl,sxrq

;

KKHcode>> ----定义变量，得到星期和日期

select top 200 a.kkhcode ,b.xingming as KKHNAME ,a.jifen,a.ye

from ret_cuxiaoka a ,ret_gukezl b

where a.gukeid=b.gukeid and a.beactive='是' and ( a.kkhcode like :value or b.shouji = :value or b.Phone = :value or b.xingming = :value)

;

huowname>> select hw,hwbh,huowname from huoweizl where duifbsh = :fdbs

;

xshy>> select dzyid as xshyid ,dzycode ,dzyname as xshy from zhiydoc where beactive='是' and issp='是' and duifbsh = :fdbs and (dzycode like '%'+ :value +'%' or zjm like '%'+ :value +'%')

;