如何在条件下使用SQL Server中的select语句

Question

我想在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个组合框补偿，但问题是我必须选择（所有）参数值来显示数据，这不是必需的，我想要的是显示数据，一旦我从任何组合框中选择（任何）值

Answer 1

通常，您可以通过将null传递给用户未设置的任何参数来实现这一点，并且查询遵循以下模式：

WHERE (@BranchID IS NULL OR tbBranches.BranchID = @BranchID)
AND (MachGroupID IS NULL OR tbMachinesGroups.MachGroupID = @MachGroupID)
AND ...