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 

The BEGIN TRANSACTION 
The SET XACT_ABORT ON 


DECLARE 
@ RptSessionID RowPointerType 
, @ ExecStr LongListType 

The EXEC dbo. InitSessionContextSp 
@ ContextName='PN_actualcost', 
@ SessionID=@ RptSessionID OUTPUT, 
@ Site=@ pSite 

DECLARE 
@ LowCharacter HighLowCharType 
, @ HighCharacter HighLowCharType 

The SET @ LowCharacter=dbo. LowCharacter () 
The SET @ HighCharacter=dbo. HighCharacter () 


The SET @ PNjobstarting=ISNULL (dbo. ExpandKyByType (' JobType, @ PNjobstarting), @ LowCharacter) 
The SET @ PNjobend=ISNULL (dbo. ExpandKyByType (' JobType, @ PNjobend), @ HighCharacter) 

The SET @ PNitemstarting=ISNULL (@ PNitemstarting dbo. LowInt ()) 
The SET @ PNitemend=ISNULL (@ PNitemend dbo. HighInt ()) 

The SET @ PNRecordDatestarting=ISNULL (@ PNRecordDatestarting, @ LowCharacter) 
The SET @ PNRecordDateend=ISNULL (@ PNRecordDateend, @ HighCharacter) 

The SET @ PNProductCodestarting=ISNULL (@ PNProductCodestarting, @ LowCharacter) 
The SET @ PNProductCodeend=ISNULL (@ PNProductCodeend, @ HighCharacter) 



SELECT dbo. Jobtran_mst. Trans_num, jobtran_mst oper_num, job, job. Item, item. The description of the item. The product_code that starts, job. Qty_released, dbo. Job. Qty_complete, item. Lbr_cost, matl_cost, fovhd_cost, vovhd_cost, trans_type, fixovhd, varovhd, jobtran_mst. RecordDate 
The FROM dbo. Jobtran_mst 
LEFT the JOIN dbo. Job ON the job. The job=jobtran_mst. The job AND the job. The suffix.=jobtran_mst suffix 
LEFT the JOIN dbo. The item ON the 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 that starts BETWEEN @ PNProductCodestarting AND @ PNProductCodeend)) 
AND jobtran_mst. Trans_type & lt;> 'M' AND fixovhd<> 0 AND varovhd<> Zero ORDER BY trans_num ASC 


COMMIT the TRANSACTION 

The EXEC dbo. CloseSessionContextSp @ SessionID=@ RptSessionID 

RETURN 0 

Want to according to the parameters of filter conditions query
1, according to the job only query
2, according to the item query only
Portfolio 3, item + RecordDate
4, item + producr_code combination query
5, item + RecordDate + producr_code combination query

Could you tell me how to achieve the above barons 1-5 of the request? These five must each in the query can be used,

CodePudding user response:

Write an if or case the when

CodePudding user response:

AND (ITEM=@ ITEM OR ISNULL (@ ITEM, ' ')=' ')
AND (RecordDate=@ RecordDate OR ISNULL (@ RecordDate, ' ')=' ')