我想在C#中使用组合框对条件进行数据过滤,所以我使用了这个存储过程:
CREATE PROCEDURE [dbo].[GET_FIXING_ON_CONDITIONS]
@BranchID int,
@MachGroupID int,
@FailGroupID int,
@FailID int,
@MachNo varchar(50),
@DateFrom date,
@DateTO date
AS
SELECT tbBranches.BranchName,
tbMachines.MachNo,
tbMachines.MachType,
tbMachinesGroups.MachGroupName,
tbFails.FailName,
tbFailsGroups.FailGroupName,
tbFailsType.FailTypeName,
tbFixing.FixDate,
tbFixing.FixDetails,
tbFixing.FixPerson
FROM tbMachines
INNER JOIN tbFixing ON tbMachines.MachNo = tbFixing.FixMachNo
AND tbMachines.BranchID = tbFixing.BranchID
INNER JOIN tbFailsGroups ON tbMachines.BranchID = tbFailsGroups.BranchID
INNER JOIN tbFailsType ON tbMachines.BranchID = tbFailsType.BranchID
INNER JOIN tbFails ON tbFixing.FixFailID = tbFails.FailID
AND tbFixing.BranchID = tbFails.BranchID
AND tbFailsGroups.FailGroupID = tbFails.FailGroupID
AND tbFailsGroups.BranchID = tbFails.BranchID
AND tbFailsType.FailTypeID = tbFails.FailType
AND tbFailsType.BranchID = tbFails.BranchID
INNER JOIN tbMachinesGroups ON tbMachines.MachGroupID = tbMachinesGroups.MachGroupID
AND tbMachines.BranchID = tbMachinesGroups.BranchID
INNER JOIN tbBranches ON tbMachines.BranchID = tbBranches.BranchID
AND tbFixing.BranchID = tbBranches.BranchID
WHERE tbBranches.BranchID = @BranchID
AND tbMachinesGroups.MachGroupID = @MachGroupID
AND tbFailsGroups.FailGroupID = @FailGroupID
AND tbFails.FailID = @FailID
AND tbMachines.MachNo = @MachNo
AND tbFixing.FixDate >= @DateFrom
AND tbFixing.FixDate <= @DateTO;
参数由7个组合框补偿,但问题是我必须选择(所有)参数值来显示数据,这不是必需的,我想要的是显示数据,一旦我从任何组合框中选择(任何)值
通常,您可以通过将null传递给用户未设置的任何参数来实现这一点,并且查询遵循以下模式:
WHERE (@BranchID IS NULL OR tbBranches.BranchID = @BranchID)
AND (MachGroupID IS NULL OR tbMachinesGroups.MachGroupID = @MachGroupID)
AND ...