create proc Search ( @ID int=null, @MgrID int=null, @StartDate datetime=null, @EndDate datetime=null, @ProName varchar(250)=null ) as select * from tb where ID= isnull(@ID,ID) and MgrID=isnull(@MgrID,MgrID) and StartDate=isnull(@StartDate,StartDate) and EndDate=isnull(@EndDate,EndDate) and ProName=isnull(@ProName,ProName)这样?再或者用case when?
中间需设一些中间变量,保存各情况下每个字段的查询语句,否则过多的if嵌套会把人弄晕。我给你一个做参考。 ALTER PROCEDURE [dbo].[searchSale] @code varchar(50), @shortName varchar(50), @empName varchar(50), @goodsName varchar(50), @goodsSN varchar(50), @price1 int, @price2 int, @storeID int, @billDate1 DateTime, @billDate2 DateTimeAS begin declare @sql as varchar(1000) declare @sqlPrice as varchar(100) declare @sqlgoodsSN as varchar(100) declare @sqlStore as varchar(100) if @price1 = 0 and @price2 = 0 set @sqlPrice = '' if @price1 = 0 and @price2 != 0 set @sqlPrice = ' and saledetail.price <='+cast(@price2 as varchar) if @price1 != 0 and @price2 = 0 set @sqlPrice = ' and saledetail.price >='+cast(@price1 as varchar) if @price1 != 0 and @price2 != 0 set @sqlPrice = ' and saledetail.price between '+cast(@price1 as varchar) + ' and '+cast(@price2 as varchar) if @goodsSN= '' set @sqlgoodsSN = '' else set @sqlgoodsSN = ' and saledetail.memo like ''%'+@goodsSN+'%''' if @storeID= 0 set @sqlStore = '' else set @sqlStore = ' and store.storeid ='+cast(@storeID as varchar) set @sql='select invoice.code as 销售单编号,client.shortname as 客户姓名,' set @sql=@sql+'goods.name as 商品名称,' set @sql=@sql+'cast(saledetail.price as decimal(10,2)) as 商品单价,' set @sql=@sql+'cast(saledetail.quantity as int) as 数量,' set @sql=@sql+'saledetail.memo as 商品编码,invoice.billdate as 销售日期,' set @sql=@sql+'employ.name as 业务员,store.name as 门店' set @sql=@sql+' from invoice' set @sql=@sql+' inner join saledetail on invoice.invoiceid=saledetail.invoiceid' set @sql=@sql+' inner join goods on saledetail.goodsid=goods.goodsid' set @sql=@sql+' inner join client on client.clientid=invoice.clientid' set @sql=@sql+' inner join store on invoice.storeid=store.storeid' set @sql=@sql+' inner join employ on invoice.exemanid=employ.empid' set @sql=@sql+' where 1=1' set @sql=@sql+' and invoice.code like ''%'+@code+'%''' set @sql=@sql+' and client.shortName like ''%'+@shortName+'%''' set @sql=@sql+' and employ.name like ''%'+@empName+'%''' set @sql=@sql+' and goods.name like ''%'+@goodsName+'%''' set @sql=@sql+@sqlgoodsSN set @sql=@sql+@sqlPrice set @sql=@sql+' and invoice.billdate between '''+cast(@billDate1 as varchar)+''' and '''+cast(@billDate2 as varchar)+'''' set @sql=@sql+@sqlStore set @sql=@sql+' order by invoice.code' exec(@sql) end
@id @name @starttime 举个例字 select * from table1 where (id = @id or @id is null) and (name =@name or @name is null) and (starttime = @starttime or @starttime is null)
create proc Search
(
@ID int=null,
@MgrID int=null,
@StartDate datetime=null,
@EndDate datetime=null,
@ProName varchar(250)=null
)
as
select * from tb where ID= isnull(@ID,ID) and MgrID=isnull(@MgrID,MgrID)
and StartDate=isnull(@StartDate,StartDate) and EndDate=isnull(@EndDate,EndDate)
and ProName=isnull(@ProName,ProName)这样?再或者用case when?
ALTER PROCEDURE [dbo].[searchSale]
@code varchar(50),
@shortName varchar(50),
@empName varchar(50),
@goodsName varchar(50),
@goodsSN varchar(50),
@price1 int,
@price2 int,
@storeID int,
@billDate1 DateTime,
@billDate2 DateTimeAS
begin
declare @sql as varchar(1000)
declare @sqlPrice as varchar(100)
declare @sqlgoodsSN as varchar(100)
declare @sqlStore as varchar(100) if @price1 = 0 and @price2 = 0
set @sqlPrice = ''
if @price1 = 0 and @price2 != 0
set @sqlPrice = ' and saledetail.price <='+cast(@price2 as varchar)
if @price1 != 0 and @price2 = 0
set @sqlPrice = ' and saledetail.price >='+cast(@price1 as varchar)
if @price1 != 0 and @price2 != 0
set @sqlPrice = ' and saledetail.price between '+cast(@price1 as varchar) + ' and '+cast(@price2 as varchar) if @goodsSN= ''
set @sqlgoodsSN = ''
else
set @sqlgoodsSN = ' and saledetail.memo like ''%'+@goodsSN+'%''' if @storeID= 0
set @sqlStore = ''
else
set @sqlStore = ' and store.storeid ='+cast(@storeID as varchar) set @sql='select invoice.code as 销售单编号,client.shortname as 客户姓名,'
set @sql=@sql+'goods.name as 商品名称,'
set @sql=@sql+'cast(saledetail.price as decimal(10,2)) as 商品单价,'
set @sql=@sql+'cast(saledetail.quantity as int) as 数量,'
set @sql=@sql+'saledetail.memo as 商品编码,invoice.billdate as 销售日期,'
set @sql=@sql+'employ.name as 业务员,store.name as 门店'
set @sql=@sql+' from invoice'
set @sql=@sql+' inner join saledetail on invoice.invoiceid=saledetail.invoiceid'
set @sql=@sql+' inner join goods on saledetail.goodsid=goods.goodsid'
set @sql=@sql+' inner join client on client.clientid=invoice.clientid'
set @sql=@sql+' inner join store on invoice.storeid=store.storeid'
set @sql=@sql+' inner join employ on invoice.exemanid=employ.empid'
set @sql=@sql+' where 1=1'
set @sql=@sql+' and invoice.code like ''%'+@code+'%'''
set @sql=@sql+' and client.shortName like ''%'+@shortName+'%'''
set @sql=@sql+' and employ.name like ''%'+@empName+'%'''
set @sql=@sql+' and goods.name like ''%'+@goodsName+'%'''
set @sql=@sql+@sqlgoodsSN
set @sql=@sql+@sqlPrice
set @sql=@sql+' and invoice.billdate between '''+cast(@billDate1 as varchar)+''' and '''+cast(@billDate2 as varchar)+''''
set @sql=@sql+@sqlStore
set @sql=@sql+' order by invoice.code' exec(@sql)
end
无论如何也不可能直接从界面拿数据然后执行sql语句的,必要的判断还是应该有的,没必要把一切都交给数据库.
=========================================================================================
@name
@starttime
举个例字
select * from table1
where (id = @id or @id is null) and (name =@name or @name is null) and (starttime = @starttime or @starttime is null)