如何在一个视图/控制器中使用连接表的多个存储过程?

Question

我创建了多个存储过程，其中包含连接操作。我正试图在我的ASP中使用这些存储过程。NET MVC应用程序，但它不能工作。我在单个视图上调用这些存储过程。我正在使用实体框架的com . to数据库。

基本上，我必须根据特定的条件调用这些存储过程，这意味着必须在单个视图的存储过程之间切换。

有人知道在一个视图中调用这样的存储过程吗?

下面是我的两个存储过程:

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

我的控制器是:

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

我的观点是:

@model IEnumerable<ComplexStoredProceduremvc.Models.sp_GetTotalRecordsByAccountNumber_Result>

在这里，如果我想按日期获取数据，那么我必须更改存储过程名称，这不是正确的选项，它只按帐号提供数据。

我希望你能明白这个问题....等待解决方案……救命!

Answer 1

我认为你可以处理这两种情况的SP如下

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

只要确保，如果你不想过滤一些parameter通过它作为0 or null。在本例中，我使用0表示varchar，使用null表示date。