We have a procedure that is dynamically filtered and based on the input parameters, it generates the filter dynamically, and in the output, if there is a record for it, it is displayed based on the result set.
CREATE PROCEDURE dbo.GetSalesWithDetails
(@HistoryID INT = NULL,
@ShDetailID INT = NULL,
@DateOfSaleFrom DateTime = NULL,
@DateOfSaleTo DateTime = NULL,
@CustomerID INT = NULL)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql Nvarchar(Max),
@Paramlist Nvarchar(Max),
@AllColumn Nvarchar(max)
SET @Sql = N'
SELECT s.HistoryID,
shd.ShDetailID,
s.DateOfSale,
s.SaleCityID,
s.SalesPersonID,
s.CustomerID,
sf.StfName,
sf.StfSname,
gd.GoodsName,
cu.CsName,
cu.CsSname,
c.CityName,
shd.GoodsQuantity,
shd.UnitPrice,
shd.Discount,
shd.Tax,
shd.OtherCosts,
shd.TotalPriceBefore,
shd.TotalPriceAfterCosts
FROM dbo.tblCity AS c INNER JOIN
dbo.tblSalesHistory AS s ON c.CityID = s.SaleCityID INNER JOIN
dbo.tblCustomers AS cu ON s.CustomerID = cu.CsID INNER JOIN
dbo.tblSalesHistoryDetails AS shd ON s.HistiryID = shd.SaleID INNER JOIN
dbo.tblStaffs AS sf ON s.SalesPersonID = sf.StfID CROSS JOIN
dbo.tblGoods AS gd
where 1 = 1 '
IF NOT @HistoryID IS NULL SET @Sql+= N' AND HistoryID = @HistoryID '
IF NOT @HistoryID IS NULL SET @Sql+= N' AND ShDetailID = @ShDetailID '
IF NOT @CustomerID IS NULL SET @Sql+= N' AND CustomerID = @CustomerID '
IF NOT @DateOfSaleFrom IS NULL SET @Sql+= N' AND DateOfSale >= @DateOfSaleFrom '
IF NOT @DateOfSaleTo IS NULL SET @Sql+= N' AND DateOfSale <= @DateOfSaleTo '
Set @Paramlist = '@HistoryID INT = NULL,
@ShDetailID INT = NULL,
@DateOfSaleFrom DateTime = NULL,
@DateOfSaleTo DateTime = NULL,
@CustomerID INT = NULL'
Exec sp_executesql @Sql ,@Paramlist,
@HistoryID,
@ShDetailID,
@DateOfSaleFrom,
@DateOfSaleTo,
@CustomerID
WITH RESULT SETS
((
SaleHistoryID INT Not Null,
ShDetailID INT Not Null,
DateOfSale DateTime Not Null,
SaleCityID INT Not Null,
SalesPersonID INT Not Null,
CustomerID INT Not Null,
StfName Nvarchar(150) Not Null,
StfSname Nvarchar(150) Not Null,
GoodsName Nvarchar(150) Not Null,
CsName Nvarchar(150) Not Null,
CsSname Nvarchar(150) Not Null,
CityName Nvarchar(150) Not Null,
GoodsQuantity Decimal(18,2) not null,
UnitPrice Decimal(18,2) not null,
Discount Decimal(18,2) not null,
Tax Decimal(18,2) not null,
OtherCosts Decimal(18,2) not null,
TotalPriceBefore Decimal(18,2) not null,
TotalPriceAfterCosts Decimal(18,2) not null
))
END
I have created the same structure used in the procedure in the Entity Framework, but I have encountered problems in creating filters with the Where condition. For example, it wants the conditions like the procedure to be used and the C# method to be able to automatically generate the filter.
public object GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
int? CustomerID = null)
{
EfM.Model1 db = new Model1();
var Query = (from row1 in db.tblSalesHistories
join row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.ShDetailID
join row3 in db.tblCities on row1.SaleCityID equals row3.CityID
join row4 in db.tblCustomers on row1.CustomerID equals row4.CsID
join row5 in db.tblStaffs on row1.SalesPersonID equals row5.StfID
join row6 in db.tblGoods on row2.GoodsID equals row6.GoodsID
select new
{
row1.HistiryID,
row2.ShDetailID,
row1.DateOfSale,
row1.SaleCityID,
row1.SalesPersonID,
row1.CustomerID,
row5.StfName,
row5.StfSname,
row6.GoodsName,
row4.CsName,
row4.CsSname,
row3.CityName,
row2.GoodsQuantity,
row2.UnitPrice,
row2.Discount,
row2.Tax,
row2.OtherCosts,
row2.TotalPriceBefore,
row2.TotalPriceAfterCosts
}
).ToList();
return Query;
}
The next thing I am facing is that in the dynamic query method in the procedure, when the procedure is complex and heavy, it is very difficult and time-consuming to fix the procedure's bugs and develop or edit it. Is there any other way to filter the list without using dynamic query?