问题请教
ALTER PROCEDURE .(
@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
, @HighCharacterHighLowCharType
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
FROMdbo.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))
ANDjobtran_mst.trans_type<>'M'AND fixovhd<>0 AND varovhd<>0ORDER 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种必须每一种在查询时都可以使用。
页:
[1]