新手请教大佬们，有SP问题请教

笑笑2016 2020-09-11 02:22:43

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种必须每一种在查询时都可以使用。

...全文