create proc pc_searchDispose
@dateone datetime,
@datetwo datetime,
@domestic bit,
@drug_type smallint,
@category smallint,
@special_composition smallint
as
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin
select sd.drug_id,convert(char(10),sd.date,23) as disDate,sd.batchNum,sd.quantity,sd.origin,sd.Reason,sd.disposeOpinion,sd.disposeMethod,sd.remark,convert(char(7),st.validity,23) as validity,
su.spuplierName,d.drug_name,d.drug_specification,d.manufacturer_id,d.units,d.costprice,d.domestic,d.drug_type,d.category,d.specia_composition,sp.class_name as types into #aa from stock_dispose sd
join drug_document d on sd.drug_id=d.drug_id
join stock_drug st on sd.drug_id=st.drug_id and sd.batchNum=st.batch_number
left join suppliers su on sd.supplierID=su.supplierId
left join system_parameter sp on d.types=sp.class_id and sp.label_id='01'
where convert(char(10),sd.date,23) between convert(char(10),@dateone,23) and convert(char(10),@datetwo,23)
select * from #aa where (@domestic is null or domestic=@domestic) and (@drug_type is null or drug_type=@drug_type) and (@category is null or category=@category) and (@special_composition is null or specia_composition=@special_composition)
drop table #aa
end
重点红色部分条件语句。
SQL还是很强大的啊,有待学习……