--写一个Store Procedure
--eg: exec test null,null,null,null,null
create proc test
(
@CountCode int,
@CustCode int,
@ProdCode int,
@Price int,
@Phone int
)
as
select ct.Name, ct.Phone, c.Name
from Customers ct
inner join Countries c on ct.CountCode=c.CountCode
inner join CustProd cp on ct.CustCode=cp.CustCode
inner join Products p cp.ProdCode=p.ProdCode
where @CountCode=case when @CountCode is null then 0 else ct.CountCode end
and @CustCode=case when @CustCode is null then 0 else ct.CustCode end
and @ProdCode =case when @ProdCode is null then 0 else p.ProdCode end
and @Price =case when @Price is null then 0 else cp.Price end
and @Phone =case when @Phone is null then 0 else ct.Phone end
--eg: exec test null,null,null,null,null
create proc test
(
@CountCode int,
@CustCode int,
@ProdCode int,
@Price int,
@Phone int
)
as
select ct.Name, ct.Phone, c.Name
from Customers ct
inner join Countries c on ct.CountCode=c.CountCode
inner join CustProd cp on ct.CustCode=cp.CustCode
inner join Products p cp.ProdCode=p.ProdCode
where @CountCode=case when @CountCode is null then 0 else ct.CountCode end
and @CustCode=case when @CustCode is null then 0 else ct.CustCode end
and @ProdCode =case when @ProdCode is null then 0 else p.ProdCode end
and @Price =case when @Price is null then 0 else cp.Price end
and @Phone =case when @Phone is null then 0 else ct.Phone end
修改为:inner join Products p on cp.ProdCode=p.ProdCode 少写on