Falsche Ausgabe aus der gespeicherten Prozedur

Ich verwende unterhalb der gespeicherten Prozedur, um einen Kristallbericht zu erzeugen. Mein Bericht überwiegt hauptsächlich data, die auf zwei möglichen Werten basieren, Arcade oder Franchise.

Ich filtere data als Arcade = 1, Franchise = 2 und Both = 0. OutletType Parameter erhalten diese int Werte.

Wenn ich für einen bestimmten Wert filtere, gibt es mir sowohl Arcade- als auch Franchise-Werte.

ALTER PROCEDURE [dbo].[PrintReceiptCancellationWorkflow] @EntryType int, @OutletType int, @RequestedUser varchar(50), @FromDate DateTime2, @ToDate DateTime2, @OutletCode varchar(10), @CancelledUser varchar(20), @Status int AS BEGIN SELECT Outlets.OutletDesc AS 'Branch', Receipt.CancelUser AS 'RequestedBy', Receipt.RecDate AS 'ReqDateTime', --ReceiptCancellationStatus.ApprovedStatus AS 'Status', ReceiptCancellationStatus.StatusDesc As Status, WorkflowRequestQueue.CposReference AS 'WCRNo', Receipt.ReceiptNo AS 'ReceiptNo', Receipt.PaymentMode AS 'PaymentMode', Receipt.AppliedAmount AS 'Amount', WorkflowRequestQueue.AppRejUser AS 'CompletedBy', WorkflowRequestQueue.AppRejDateTime AS 'CompletedDateTime', EntryTypes.EntryType AS 'PaymentCategory', WorkflowRequestQueue.AppRejComment AS 'Comment', OutletCategories.CatDesc As 'OutletType' FROM Receipt INNER JOIN Outlets ON Receipt.OutletCode = Outlets.OutletCode LEFT OUTER JOIN EntryTypes ON Receipt.EntryTypeId = EntryTypes.Id LEFT OUTER JOIN WorkflowRequestQueue ON Receipt.CposReference = WorkflowRequestQueue.CposReference LEFT OUTER JOIN OutletCategories ON Outlets.OutletType = OutletCategories.CatCode LEFT OUTER JOIN ReceiptCancellationStatus ON WorkflowRequestQueue.ApprovedStatus = ReceiptCancellationStatus.Id WHERE (Outlets.OutletType = @OutletType OR Outlets.OutletType = 0) OR (Receipt.EntryTypeId = @EntryType OR Receipt.EntryTypeId = 0) OR Receipt.CancelUser = @RequestedUser OR (( CONVERT(DATE,WorkflowRequestQueue.AppRejDateTime) >= @FromDate) AND ( CONVERT(DATE,WorkflowRequestQueue.AppRejDateTime) <= @ToDate)) OR Outlets.OutletCode = @OutletCode OR WorkflowRequestQueue.ApprovedPerson = @CancelledUser OR (WorkflowRequestQueue.ApprovedStatus = @Status OR WorkflowRequestQueue.ApprovedStatus = 0) END 

Outlet-Typ Dropdown-Werte,

  $scope.VendorDropdown = [ { "text": "Select", "value": "0" }, { "text": "Arcade", "value": "1" }, { "text": "Franchise", "value": "2" }, ]; 

Berichtsgenerierungscode,

  object paymentCategory = Convert.ToInt32(Request.Form["PaymentCategory"]); object vendor = Convert.ToInt32(Request.Form["Vendor"]); object requestedUser = Convert.ToString(Request.Form["RequestedUser"]); object cancelledUser = Convert.ToString(Request.Form["CancelledUser"]); object Fromdate = Convert.ToDateTime(Request.Form["dateFrom"]); object Todate = Convert.ToDateTime(Request.Form["dateTo"]); object status = Convert.ToInt32(Request.Form["Status"]); object outlet = Convert.ToString(Request.Form["Outlet"]); if (isExcel) { myReport.Load(server.MapPath("~/CPOSReport/MIS/CancellationReports/ReceiptCancellationWorkFlow.rpt")); } else { myReport.Load(server.MapPath("~/CPOSReport/MIS/CancellationReports/ReceiptCancellationWorkFlow.rpt")); } myReport.SetParameterValue("@EntryType", paymentCategory); myReport.SetParameterValue("@OutletType",vendor ); myReport.SetParameterValue("@RequestedUser", requestedUser); myReport.SetParameterValue("@CancelledUser", cancelledUser); myReport.SetParameterValue("@FromDate", Fromdate); myReport.SetParameterValue("@ToDate", Todate); myReport.SetParameterValue("@Status", status); myReport.SetParameterValue("@OutletCode", outlet); 

Alle Ihre wo Bedingungen sind OR'ed. Dies bedeutet, dass, wenn einer von ihnen zufrieden ist, wird die Zeile aufgenommen werden.

Du solltest das wohl probieren, woher du stattdessen mit den verschiedenen Bedingungen zusammenkommst:

 WHERE (Outlets.OutletType = @OutletType OR Outlets.OutletType = 0) AND (Receipt.EntryTypeId = @EntryType OR Receipt.EntryTypeId = 0) AND Receipt.CancelUser = @RequestedUser AND ( ( CONVERT(DATE,WorkflowRequestQueue.AppRejDateTime) >= @FromDate) AND ( CONVERT(DATE,WorkflowRequestQueue.AppRejDateTime) <= @ToDate) ) AND Outlets.OutletCode = @OutletCode AND WorkflowRequestQueue.ApprovedPerson = @CancelledUser AND (WorkflowRequestQueue.ApprovedStatus = @Status OR WorkflowRequestQueue.ApprovedStatus = 0)