问题请教

mis2015

[SQL] 纯文本查看 复制代码

ALTER PROCEDURE [dbo].[PN_actualcost]  
(  
@PNjobstarting               JobType = NULL,  
@PNjobend                    JobType = NULL,  
@PNitemstarting              ItemType = NULL ,  
@PNitemend                   ItemType = NULL ,  
@PNRecordDatestarting        CurrentDateType=NULL ,  
@PNRecordDateend             CurrentDateType=NULL ,  
@PNProductCodestarting       ProductCodeType=NULL ,  
@PNProductCodeend            ProductCodeType=NULL    ,
 @pSite             SiteType       = NULL  
)  
AS  
  
BEGIN TRANSACTION  
SET XACT_ABORT ON  
  
  
DECLARE  
  @RptSessionID RowPointerType  
  ,@ExecStr LongListType  
  
EXEC dbo.InitSessionContextSp     
         @ContextName = 'PN_actualcost',  
         @SessionID = @RptSessionID OUTPUT,  
        @Site = @pSite  
  
 DECLARE  
   @LowCharacter     HighLowCharType  
 , @HighCharacter  HighLowCharType  
  
SET @LowCharacter = dbo.LowCharacter()  
SET @HighCharacter = dbo.HighCharacter()  
  
      
SET @PNjobstarting = ISNULL( dbo.ExpandKyByType('JobType', @PNjobstarting), @LowCharacter)  
SET @PNjobend = ISNULL( dbo.ExpandKyByType('JobType', @PNjobend), @HighCharacter)  
     
SET @PNitemstarting=ISNULL( @PNitemstarting, dbo.LowInt())  
SET @PNitemend=ISNULL( @PNitemend, dbo.HighInt())  
  
SET @PNRecordDatestarting=ISNULL(@PNRecordDatestarting, @LowCharacter)  
SET @PNRecordDateend=ISNULL(@PNRecordDateend, @HighCharacter)  
  
SET @PNProductCodestarting=ISNULL(@PNProductCodestarting, @LowCharacter)  
SET @PNProductCodeend=ISNULL(@PNProductCodeend, @HighCharacter)  
  
  
  
SELECT dbo.jobtran_mst.trans_num, jobtran_mst.job,oper_num ,job.item,item.description,item.product_code,job.qty_released,dbo.job.qty_complete,item.lbr_cost,matl_cost,fovhd_cost,vovhd_cost, trans_type,fixovhd,varovhd,jobtran_mst.RecordDate  
FROM  dbo.jobtran_mst   
LEFT JOIN dbo.job ON job.job = jobtran_mst.job AND job.suffix = jobtran_mst.suffix  
LEFT JOIN dbo.item ON item.item = job.item  
WHERE
((jobtran_mst.job BETWEEN @PNjobstarting AND @PNjobend)  
AND(job.item BETWEEN @PNitemstarting AND @PNitemend)  
AND (jobtran_mst.RecordDate BETWEEN @PNRecordDatestarting AND @PNRecordDateend)  
OR (product_code BETWEEN @PNProductCodestarting AND @PNProductCodeend))  
AND  jobtran_mst.trans_type<>'M'AND fixovhd<>0 AND varovhd<>0  ORDER BY trans_num ASC  
  
  
COMMIT TRANSACTION  
  
EXEC dbo.CloseSessionContextSp @SessionID = @RptSessionID  
  
RETURN 0

想根据参数进行过滤条件查询
1、只根据job查询
2、只根据item查询
3、item+RecordDate组合查询
4、item+producr_code组合查询
5、item+RecordDate+producr_code组合查询

请问大佬们怎么实现上面1-5条的要求？这5种必须每一种在查询时都可以使用。