CODEWITHSUNDEEP
sql-server
Darshani Kaushalya
Darshani Kaushalya

Reputation: 125

Incorrect output from stored procedure

I am using below stored procedure to generate a crystal report.My report mainly filters data based on two possible values, Arcade or Franchise.

I filter data as Arcade = 1 , Franchise = 2 and Both = 0.The OutletType parameter get these int values.

When I filter for one particular value It gives me both arcade and franchise values.

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 type drop down values,

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

        ];

Report generation code,

     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);

Upvotes: 0

Views: 43

Answers (1)

larsts
larsts

Reputation: 451

All of your where conditions are OR'ed. This means that if one of them are satisfied, the row will be included.

You should probably try this WHERE in stead with the different conditions AND'ed together:

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)

Upvotes: 1

Related Questions