sql server - How to use multiple stored procedures with join tables in one view/controller?

Question

I have created multiple stored procedures with join operations in it. I am trying to use these stored procedures in my ASP.NET MVC application but it's not working. I am calling these stored procedures on single view. I am using Entity Framework for comm. to database.

Basically, I have to call these stored procedures bases on particular condition that means have to switch between stored procedures on single view.

Does anyone have idea to call such stored procedure in a single view?

Here are my two stored procedures:

CREATE PROCEDURE sp_GetTotalRecordsByAccountNumber
    @branch varchar(4),
    @basic varchar(6),
    @suffix varchar(3)
AS 
BEGIN
    SELECT
        Date AS 'Date',
        desc1 + RTRIM(LTRIM(ISNULL(desc2, ''))) + '' +
                RTRIM(LTRIM(ISNULL(desc2, ''))) AS 'Description',
        ISNULL(CASE WHEN Amount < 0 THEN (Transactionamt / Currency) END, 0) AS Debit,
        ISNULL(CASE WHEN Amount > 0 THEN (Transactionamt / Currency) END, 0) AS Credit,
        (TotalAmount / currency) AS Balance
    FROM
        tbl1
    LEFT OUTER JOIN 
        tbl2 ON tbl1.accountnumber = tbl2.accountnumber
    LEFT OUTER JOIN 
        tbl3 ON tbl1.currency = tbl3.currency
    LEFT OUTER JOIN 
        tbl4 ON tbl1.accountName = tbl4.accountName
    WHERE
        branch = @branch 
        AND basic = @basic 
        AND suffix = @suffix
    ORDER BY 
        date 
END

CREATE PROCEDURE sp_GetTotalRecordsByDates
    @branch varchar(4),
    @basic varchar(6),
    @suffix varchar(3),
    @startdate datetime,
    @enddate datetime
AS
BEGIN
    SELECT 
        Date AS 'Date',
        desc1 + RTRIM(LTRIM(ISNULL(desc2, ''))) + '' +
                RTRIM(LTRIM(ISNULL(desc2, ''))) AS 'Description',
        ISNULL(CASE WHEN Amount < 0 THEN (Transactionamt/Currency) END, 0) AS Debit,
        ISNULL(CASE WHEN Amount > 0 THEN (Transactionamt/Currency) END, 0) AS Credit,
        (TotalAmount/currency) AS Balance
    FROM
        tbl1
    LEFT OUTER JOIN 
        tbl2 ON tbl1.accountnumber = tbl2.accountnumber
    LEFT OUTER JOIN 
        tbl3 ON tbl1.currency = tbl3.currency
    LEFT OUTER JOIN 
        tbl4 ON tbl1.accountName = tbl4.accountName
    WHERE 
        branch = @branch 
        AND basic = @basic 
        AND suffix = @suffix
        AND date BETWEEN @startdate AND @enddate
    ORDER BY 
        date date
END

My controller is:

[HttpPost]
public ActionResult Index(FormCollection formCollection, string accountNo,string date,bool allcheckbox) 
{  
    if(allcheckbox == true) 
    {
       var dc = db.sp_GetTotalRecordsByAccountNumber(accountNo).ToList();
    }
    else
    {
       var dc = db.sp_GetTotalRecordsByDates(accountNo).ToList();
    }

    return view("Index",dc);
}

And my view is:

@model IEnumerable<ComplexStoredProceduremvc.Models.sp_GetTotalRecordsByAccountNumber_Result>

Here, it only gives data by account number if I want to get data by date then I have to change the stored procedure name which is not the correct option.

I hope you will get the problem....waiting for some solutions...Help!

question from:https://stackoverflow.com/questions/65857417/how-to-use-multiple-stored-procedures-with-join-tables-in-one-view-controller

Answer 1

I think you can handle both situation with one SP like below

CREATE PROCEDURE sp_GetTotalRecordsByDates
@branch varchar(4)='0',
@basic varchar(6)='0',
@suffix varchar(3)='0',
@startdate datetime=null,
@enddate datetime=null
AS
BEGIN
SELECT 
    Date AS 'Date',
    desc1 + RTRIM(LTRIM(ISNULL(desc2, ''))) + '' +
            RTRIM(LTRIM(ISNULL(desc2, ''))) AS 'Description',
    ISNULL(CASE WHEN Amount < 0 THEN (Transactionamt/Currency) END, 0) AS Debit,
    ISNULL(CASE WHEN Amount > 0 THEN (Transactionamt/Currency) END, 0) AS Credit,
    (TotalAmount/currency) AS Balance
FROM
    tbl1
LEFT OUTER JOIN 
    tbl2 ON tbl1.accountnumber = tbl2.accountnumber
LEFT OUTER JOIN 
    tbl3 ON tbl1.currency = tbl3.currency
LEFT OUTER JOIN 
    tbl4 ON tbl1.accountName = tbl4.accountName
WHERE 
    (branch = @branch or @branch='0')
    AND (basic = @basic or @basic='0')
    AND (suffix = @suffix or @suffix ='0')
    AND (date >= @startdate or @startdate is null)
    AND (date <= @enddate or @enddate is null)
ORDER BY 
    date
END

Just make sure if you do not want to filter with some parameter pass it as 0 or null. In this case I used 0 for varchar and null for date.